locked
XML With Carriage Return in ExecuteSQL task RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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