Insert entire XML into database

[Former Community Member]

I have created a form variable "frmData" , and an init form .xdp with XML schema data binding.

After the user filled in the form and submitted, is there any way I can retrieve the xml and store the entire xml into database(MS SQL)?

I'm using Database QPAC (jdbc.jar) to insert value.



I tried the following SQL statement but the value stored is empty.



INSERT into ClaimDetails(formData)

values ('{$/process_data/frmData$}');



Any idea?



Thanks,

Leia

[HowardTreisman]

Hi

Try something like:

{$ serialize(/process_data/frmData/form-data/data/*,false) $}



I haven't actually tried it, but I think it should work.



{$ serialize(/process_data/frmData/form-data/data/xdp/datasets/data/*, false) $}

will give you results slightly deeper in the hierarchy.



Bear in mind, if your XML data contains a quote (') character, it will prematurely complete your values clause and cause a SQL error.



Howard

[Former Community Member]

Hello -



Yes I would agree with Howard, I would approach this problem using the Serialize function.



If you find that you do have things you need to escape in your data you can try using the "translate" String XPath function. You could also of course write some simple java code in a script QPac to accomplish this.



Good luck!



Will@Adobe

[Former Community Member]

Thanks for helping~ will try it later...

btw, any good reference for the syntax of XPATH?

leia

[HowardTreisman]

Try: http://www.w3schools.com/xpath/default.asp

Howard

http://www.avoka.com

[Former Community Member]

Hi all,



I have just tried the code and it works very fine! Many many thanks...



one question, why is it the syntax of XPATH is abit different from

w3c? for example, why does this "{$" (curly bracket and dollar sign) mean? is it only for use in Workflow database QPAC?



thanks again



cheers,

leia

[HowardTreisman]

Hi Leia



The {$...$} syntax is not part of Xpath, it's specific to Workflow. It's kindof like a mail-merge syntax, that allows you to embed an xpath expression into a string.



A good example is in the email qpac, in the Subject line, you might type: "RE: Your purchase"



Or, if you wanted to embed the purchase order number into the subject line, you could type:

"RE: PO Number: {$/process_data/@po_number$} - your purchase"



Workflow will locate any string between the {$ and $}, and try to evaluate it as an xpath expression.



So your email might go out with a subject of:

"RE: PO Number: 112233 - your purchase"



In your case, the xpath expression is a little more complex, but it's essentially the same thing.



If you used

INSERT into ClaimDetails(formData)

values ('/process_data/frmData');



then the value you inserted into the table would be '/process_data/frmData' - the {$ $} is the hint to workflow that it should evaluate the xpath expression.



Hope this helps...

Howard

[Former Community Member]

Could you please explain how to use 'Serialize' funtion in script QPAC.

[HowardTreisman]

The serialize xpath function will turn a DOM (XML) document (or a set of nodes) into the xml string representation of that document.



Howard