Page 1 of 1
xml pickup to call a stored procedure
Posted: Mon Jan 04, 2016 3:15 pm
by 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
Re: xml pickup to call a stored procedure
Posted: Mon Jan 04, 2016 3:34 pm
by sander
Use 'Metadata is asset' as pickup mode. That way you can use the XML metadata via the Metadata.Text etc. variables.
Re: xml pickup to call a stored procedure
Posted: Mon Jan 04, 2016 4:49 pm
by 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
Re: xml pickup to call a stored procedure
Posted: Mon Jan 04, 2016 4:58 pm
by 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;
Re: xml pickup to call a stored procedure
Posted: Mon Jan 04, 2016 6:05 pm
by 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
Re: xml pickup to call a stored procedure
Posted: Tue Jan 05, 2016 10:06 am
by 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.