xml pickup to call a stored procedure

[The Bidd]

Hi

I am new to Switch so am looking for some advice on how I can get data from my xml file and use in in a call to by database.

So far I have an xml pickup set to "metadata alongside asset" this then passes to the database connect.
From this I am calling a stored procedure that passes two variables into the database and returns an email address if true or a defuaul email is false
The SQL side is not a problem but I can't get the xml data to pickup?

This is what I have so far (Free Typed)

Code: Select all

CALL myprocedure_SP_switchLookup ([Metadata.Text:Path="/ValueDescription/MF_2",Dataset="xml",Model="XML"], [Metadata.Text:Path="/ValueDescription/MF_1",Dataset="xml",Model="XML"])

Hopefully this is a simple issue and someone can help?

Thanks in advance and Happy New Year to all

Bidd

[sander]

Use 'Metadata is asset' as pickup mode. That way you can use the XML metadata via the Metadata.Text etc. variables.

[The Bidd]

Hi Sander

Thanks for the quick response.

I have changed the setting but it still does not show the data in the "Build location path" to allow me to pick it up?
In the SQL Statement I select "Define Variables > Metadata-Text >Path >Build Location Path and select the xml file.
From here I expected to be able to select the data from the xml but its currently empty!
The only thing showing in the data tree is rdf:Description

This is My XML file

Code: Select all

<?xml version="1.0"?>

-<ValueDescription Type="metadatafields">

<MF_1 Type="string" ReadOnly="Yes" LocalizedTagName="Customer Name" ValueIsRequired="Yes" Tooltip="Customer Name" Default="" Editor="inline" RememberLastValue="No" Format="" Subtype="inline">Customer Testing</MF_1>

<MF_2 Type="string" ReadOnly="No" LocalizedTagName="Job Number" ValueIsRequired="Yes" Tooltip="Job Number" Default="" Editor="inline" RememberLastValue="No" Format="" Subtype="inline">80000</MF_2>

<PS_1 Type="enum:Pass;Fail" ReadOnly="No" LocalizedTagName="Preflight status" ValueIsRequired="Yes" Tooltip="This field reports the status of preflighted document" Editor="inline" RememberLastValue="No" Subtype="inline">Pass</PS_1>

<PS_2 Type="enum:Yes;No" ReadOnly="No" LocalizedTagName="Sign-off" ValueIsRequired="Yes" Tooltip="This field reports Sign-Off of preflighted document" Editor="inline" RememberLastValue="No" Subtype="inline">No</PS_2>

</ValueDescription>

What am I missing?

Thanks

Bidd

[sander]

The XML has first to be picked up before you can select it in build location path. What I usually do is put the arrow after the XML pickup folder on hold, stop the flow, and go to build location path.




Edit, also your XML is corrupt at line 3, the - shouldn't be there;

[The Bidd]

Ok That worked perfectly - Big Thanks

I can now see the data in the builder and select what I want.
My Procedure call looks like this (Is this correct when calling two variables from switch?)

Code: Select all

CALL myprocedure_SP_switchLookup(' [Metadata.Text:Path="/ValueDescription/MF_2",Dataset="Xml",Model="XML"] , [Metadata.Text:Path="/ValueDescription/MF_1",Dataset="Xml",Model="XML"]')

When I pass the file in it goes to the fail folder, Where do I get the response from?
I should either get the email address for the manager responsible for the job or a default email.

Thanks Again

Bidd

[sander]

Set the arrow after your database connect to;
Carry this type of jobs: Log
Success out: Yes
Error out: Yes

Then you get a XML logfile which shows what SQL statement is actually executed.