SSIS - XML result set

  • 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 ?

  • 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

  • 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>

  • 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

  • I have already created XML string from table.

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

  • 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.

  • 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

  • I did this in Script, which resolved the issue!

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

  • 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

  • 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