March 20, 2012 at 5:07 am
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 ?
March 20, 2012 at 5:22 am
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
March 22, 2012 at 4:26 am
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>
March 22, 2012 at 4:31 am
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
March 22, 2012 at 4:42 am
I have already created XML string from table.
I just want to remove the <ROOT></ROOT> tag from my XML string
March 22, 2012 at 4:44 am
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.
March 27, 2012 at 10:26 pm
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
March 28, 2012 at 2:44 am
I did this in Script, which resolved the issue!
Dts.Variables["xmlout"].Value = Dts.Variables["XML"].Value.ToString().Replace("<ROOT>","").Replace("</ROOT>","");
July 19, 2012 at 9:12 pm
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
March 13, 2014 at 8:31 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply