SSIS - XML result set

Question

SSIS - XML result set

Gerbera

SSCommitted

Points: 1715
More actions
March 20, 2012 at 5:07 am

#147031

I am trying to store the XML resultset from Execute SQL Task in a String variable.
The following value is stored in the String variable <ROOT> <Generated XML string\> <ROOT\>
I only need <Generated XML string\>, I dont want ROOT tag to get appended.
What should I do ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GRussell31 SSCrazy Points: 2116 More actions · Answer 1

If you change the SQL task to return a simple hard coded string as a test (for example <test>testing</test>) does this append the root node?

I've done something similar and never had this issue (although it wasn't exactly the same).

Graham

Gerbera SSCommitted Points: 1715 More actions · Answer 2

Its appending ROOT node for that too !!!

I did

Select '<test>testing</test>'

Result set : XML

Result set variable : Test - String datatype

It gave me <ROOT> <test>testing</test></ROOT>

GRussell31 SSCrazy Points: 2116 More actions · Answer 3

Just found this article, which does something similar but saves the XML to a file.

Does it work for you?

http://www.rad.pasfu.com/index.php?/archives/25-SSIS-Sql-Server-to-XML-Save-to-file.html

Graham

Gerbera SSCommitted Points: 1715 More actions · Answer 4

I have already created XML string from table.

I just want to remove the <ROOT></ROOT> tag from my XML string

GRussell31 SSCrazy Points: 2116 More actions · Answer 5

I was wondering if the approach used in the article saved the XML with the root node as well as I'm trying to understand what is adding it.

Gerbera SSCommitted Points: 1715 More actions · Answer 6

Yes.. Even with the XML generated using FOR XML RAW , "<ROOT></ROOT> " is getting appended when using the query in EXECUTE SQL task with XML resultset

Gerbera SSCommitted Points: 1715 More actions · Answer 7

I did this in Script, which resolved the issue!

Dts.Variables["xmlout"].Value = Dts.Variables["XML"].Value.ToString().Replace("<ROOT>","").Replace("</ROOT>","");

waqas_kk Valued Member Points: 57 More actions · Answer 8

hi i have the same problem as well i am kind of new to SSIS and xml

can you pls help

i just need to remove root tags too

mohitsikri Valued Member Points: 61 More actions · Answer 9

You could define a second variable, and set it to be based on the following expression:

REPLACE(REPLACE(@[User::YourXMLVariable], "<ROOT>", ""), "</ROOT>", "")

and use this as the source to your Xml file