none
How to add one new root node to an existing XML document in SSIS?

    Question

  • Hello,

    Actually my XML is like this:

    <?xml version="1.0" encoding="utf-16"?>

    <ArrayOfString>

    <string>value1</string>

            <string>value2</string>

    </ArrayOfString>

    I need to, using some component of SSIS (XML task, or script), to wrap in one new root node <datarows> like:

    <?xml version="1.0" encoding="utf-16"?>

    <datarows>

    <ArrayOfString>

        <string>value1</string>

        <string>value2</string>

    </ArrayOfString>

    </datarows>

    Can anyone tell me how to do this?

    Thanks in advance.

    Friday, April 26, 2013 11:18 AM

Answers

  • Using XML Task. Set the properties as below. Once you set the destination to a variable use Script Task. Set the source Type accordingly if you are using an xml file. 


    Regards, RSingh


    Friday, April 26, 2013 2:02 PM
  • In your script task set the properties of the output variable as readwrite. Finally use the code to replace the desire string.
    --------------------
    Public Sub Main()
            Dim sid As String = CStr(Dts.Variables("FSessionID").Value)
            Dts.Variables("FSessionID").Value = Replace(Replace(Left(sid, Len(sid) - 2), "}", ""), "{", "")
            MsgBox(Dts.Variables("FSessionID").Value)
            Dts.TaskResult = Dts.Results.Success
    End Sub


    Regards, RSingh

    Friday, April 26, 2013 2:10 PM

All replies

  • Assign the XML to a SSIS variable. Use Script Task or Expression to replace the sub string <ArrayOfString> as <datarow><ArrayOfString> and similarly </ArrayOfString> with </ArrayOfString></datarow>

    Regards, RSingh

    Friday, April 26, 2013 11:47 AM
  • Assign the XML to a SSIS variable. Use Script Task or Expression to replace the sub string <ArrayOfString> as <datarow><ArrayOfString> and similarly </ArrayOfString> with </ArrayOfString></datarow>

    Regards, RSingh

    RSingh, thanks for the great idea.

    Sorry for my ignorance, but how can i assign the XML to a variable? And which datatype should it be?

    After that, how can i use Expression to make this replacement?

    Friday, April 26, 2013 12:19 PM
  • I tried to do this using a VB script task component, with this code:

     Dim fileReader As String = My.Computer.FileSystem.ReadAllText("C:\Users\guilhermesm\AdmDados\Cargas\Protocolo Sigiloso\protocoloSigiloso\protocoloSigiloso\listaProtocolosSigilosos.xml").Replace("<ArrayOfString xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">", "<datarows> <ArrayOfString xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">")

            My.Computer.FileSystem.WriteAllText("C:\Users\guilhermesm\AdmDados\Cargas\Protocolo Sigiloso\protocoloSigiloso\protocoloSigiloso\listaProtocolosSigilosos.xml", fileReader, False)

            Dim fileReader2 As String = My.Computer.FileSystem.ReadAllText("C:\Users\guilhermesm\AdmDados\Cargas\Protocolo Sigiloso\protocoloSigiloso\protocoloSigiloso\listaProtocolosSigilosos.xml").Replace("</ArrayOfString>", "</ArrayOfString> </datarows>")

            My.Computer.FileSystem.WriteAllText("C:\Users\guilhermesm\AdmDados\Cargas\Protocolo Sigiloso\protocoloSigiloso\protocoloSigiloso\listaProtocolosSigilosos.xml", fileReader2, False)

            Dts.TaskResult = ScriptResults.Success

    It correctly do the replacements.

    But when i tried to generate the XSD on the XML source component, i get this error:

    "There is no Unicode byte order mark. Cannot switch to Unicode."

    Is there a way to fix my VB code??

    Thanks.

    Friday, April 26, 2013 1:54 PM
  • Using XML Task. Set the properties as below. Once you set the destination to a variable use Script Task. Set the source Type accordingly if you are using an xml file. 


    Regards, RSingh


    Friday, April 26, 2013 2:02 PM
  • In your script task set the properties of the output variable as readwrite. Finally use the code to replace the desire string.
    --------------------
    Public Sub Main()
            Dim sid As String = CStr(Dts.Variables("FSessionID").Value)
            Dts.Variables("FSessionID").Value = Replace(Replace(Left(sid, Len(sid) - 2), "}", ""), "{", "")
            MsgBox(Dts.Variables("FSessionID").Value)
            Dts.TaskResult = Dts.Results.Success
    End Sub


    Regards, RSingh

    Friday, April 26, 2013 2:10 PM
  • RSingh, thank you very much for your attention, it worked!

    Friday, April 26, 2013 2:25 PM