locked
XML With Carriage Return in ExecuteSQL task RRS feed

  • Question

  • Hi,

    I want to have an XML resultset in the Execute SQL task and then place the resultset to Send Mail message with Carriage Return:

    I have the following steps:

    Execute SQL

    SQLStatement:

    select distinct col1, col2
    from table1
    WHERE col1 = 203
    GROUP BY col1, col2
    FOR XML AUTO;

    Resultset: XML

    Resultset Name: 0; Variable Name: User::Result1

    XML Task:

    Operation Type: XPath

    Source Type: Variable

    Source: User::Result1

    Destination: User::Count1

    Second Operand Type: Direct Input

    Second Operand: count(//table1)

    Send Mail Task:

    Message Source: @[User::Result1]

    Everything works fine, except that the result does not have a carriage return:

    <ROOT><table1 col1="203" col2="Spare 203"/><table1 col1="203" col2="SPEED"/><table1 col1="203" col2="SPEED 4545"/></ROOT>

    How can I make the result to be:

    <ROOT>

    <table1 col1="203" col2="Spare 203"/>

    <table1 col1="203" col2="SPEED"/>

    <table1 col1="203" col2="SPEED 4545"/>

    </ROOT>


    cherriesh

    Friday, August 16, 2013 4:11 AM

Answers

  • The issue is, that the FOR XML AUTO returns the data without white spaces.

    You can reformat the XML for example in the Script Task using below code:

    public void Main()
    {
        string xmlSrc = this.Dts.Variables["Result1"].Value.ToString();
    
        StringBuilder sb = new StringBuilder();
                
        XmlWriterSettings s = new XmlWriterSettings();
        s.NewLineOnAttributes = true;
        s.Indent = true;
        using (XmlWriter xwrt = XmlWriter.Create(sb, s))
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(xmlSrc);
            doc.WriteTo(xwrt);
        }
    
        this.Dts.Variables["Result1"].Value = sb.ToString();
    
    	Dts.TaskResult = (int)ScriptResults.Success;
    }
    

    You need to put the Result1 as Read/Write variable to the Script Task.
    • Proposed as answer by Mike Yin Monday, August 19, 2013 2:44 PM
    • Marked as answer by Mike Yin Sunday, August 25, 2013 2:14 PM
    Friday, August 16, 2013 10:08 AM