How to convert a text datatype object to XML datatype

[Former Member]

Hi

We have saved an XML file from BLS in a MS SQL Database as ntext datatype.

Then the stored data is retrieved from SQL DB using SQL Query.

Now we want to make this data to be shown as XML Document output

Here we tried folowing steps in BLs

1. Retrieve the Data (as ntext datatype) from SQL DB using SQL Query

2. Assign the retrieved data to a local variable as XML DataType with XPath "/Rowsets"

3. Loop the assigned local variable using Repeater with XPath Expression as Local.Variable{/Rowsets/Rowset/Row}

4. Use Row action to append the looped row data to the XML output Document.

But no proper result as expected is shown in the output document.

Also we tried this conversion using xmlencode and xmldecode functions defined in Link Editor such as xmldecode(<GetDataFromSQLDB>.Results{/Rowsets/Rowset/Row/Data}), but no improvement.

Any help pls with ideas

Regards

Som

[salvatore_castro]

Som,

Assigning the string value, as long as it's valid XML, to and XML property should be all you have to do. The Row action will work with any XML structure that uses /Rowsets/Rowset/Row so you could 'configure' this action to point to the local property directly.

-Sam

[Former Member]

Hi

If the xml stored in DB is not that much complex.Why dont you store as a comma seperated values.And while retrieving from BLS you can stringListtoXMLparser action to create xml Document.It's just a suggestion.

Regards,

Ravi Kumar

[Former Member]

hi,

Test the SQL Query template with text/xml mode. Here you can see the xml returned from DB.

Just check that xml and proceed.

What I think is,

when you execute a select query through SL query mode it will automatically return in xMII XML format. I never tried with ntext datatype.

May I know why are you using XML decode and Encode. xml Encode change xml in to string type. xml decode will give you xml structure but it is again string data type. SAVE this using notepad and try to lad the same with XML loader.

Hope this helps

Regards,

Kishore kumar P.S.