t-sql nested xml with xml explicit mode

  • rayan98

    Mr or Mrs. 500

    Points: 577

    Hi guys, thanks for your time reading this .

    at the moment i m just trying to find a way to produce something similar to this by using "for xml"

    <TEST>

    <TESTING>

    <comment>acc</comment>

    <number>1</number>

    <boo>1</boo>

    <INNERNODE>

    <exe><![CDATA[aacc]]></exe>

    </INNERNODE>

    </TESTING>

    </TEST>

    and i have tried to use a for xml to do this by nested a for xml within the query

    select

    C_Comment as [comment],

    i_number as [number],

    boolean as [boo]

    , (

    SELECT

    1 as [tag]

    ,0 as [parent]

    ,C_Comment as [comment!1!!exe]

    FROM

    TEST_FOR_XML

    FOR XML EXPLICIT

    ) AS [INNERNODE]

    FROM

    TEST_FOR_XML

    FOR XML PATH('TESTING'), ROOT('TEST')

    and within the nested node,

    it converted the xml tags < and > into the < and &bt;

    am i going on the wrong track or i m missing the syntax ...

    many thanks for all the help guys

  • andrewd.smith

    SSCrazy Eights

    Points: 8444

    Can you provide your source data table structure TEST_FOR_XML and some test data in a readily consumable form, i.e. as insert statements.

    Are you sure you need the CDATA section? If you were to give up that requirement, I believe you you generate your XML fairly easily with the FOR XML PATH.

    If you really need the CDATA section, you are forced into using the ugly FOR XML EXPLICIT syntax.

    Since FOR XML will automatically encode text content characters such as < > and & as &lt; &gt; and &amp; respectively, a CDATA section shouldn't be necessary.

  • andrewd.smith

    SSCrazy Eights

    Points: 8444

    OK, I've made the assumption that your table structure is something like the following:

    CREATE TABLE #TEST_FOR_XML (

    id int PRIMARY KEY,

    C_Comment varchar(100),

    i_number int,

    boolean bit

    )

    Here are some test data, including some characters that need XML entity escaping.

    INSERT INTO #TEST_FOR_XML (id, C_Comment, i_number, boolean)

    SELECT 10, 'acc', 1, 1 UNION ALL

    SELECT 11, 'BLAH', 17, 0 UNION ALL

    SELECT 12, '<''"&>', 112, 1

    This method uses FOR XML PATH. It cannot be made to generate a CDATA section.

    SELECT

    TOUT.C_Comment as [comment],

    TOUT.i_number as [number],

    TOUT.boolean as [boo],

    (

    SELECT TIN.C_Comment AS [exe]

    FROM #TEST_FOR_XML TIN

    WHERE (TIN.id = TOUT.id)

    FOR XML PATH('INNERNODE'), TYPE

    )

    FROM #TEST_FOR_XML TOUT

    FOR XML PATH('TESTING'), ROOT('TEST')

    Here are the results:

    <TEST>

    <TESTING>

    <comment>acc</comment>

    <number>1</number>

    <boo>1</boo>

    <INNERNODE>

    <exe>acc</exe>

    </INNERNODE>

    </TESTING>

    <TESTING>

    <comment>BLAH</comment>

    <number>17</number>

    <boo>0</boo>

    <INNERNODE>

    <exe>BLAH</exe>

    </INNERNODE>

    </TESTING>

    <TESTING>

    <comment>&lt;'"&amp;&gt;</comment>

    <number>112</number>

    <boo>1</boo>

    <INNERNODE>

    <exe>&lt;'"&amp;&gt;</exe>

    </INNERNODE>

    </TESTING>

    </TEST>

    This method uses FOR XML EXPLICIT, and uses a CDATA section. The FOR XML EXPLICIT syntax is definitely not intuitive, and you have to be careful to order the unioned rows correctly to avoid child elements ending up within the wrong parent elements.

    SELECT

    1 AS Tag,

    0 AS Parent,

    id AS [TESTING!1!id!hide],

    C_Comment as [TESTING!1!comment!element],

    i_number as [TESTING!1!number!element],

    boolean as [TESTING!1!boo!element],

    NULL AS [INTERNODE!2!exe!cdata]

    FROM #TEST_FOR_XML

    UNION ALL

    SELECT

    2 AS Tag,

    1 AS Parent,

    id,

    NULL,

    NULL,

    NULL,

    C_Comment

    FROM #TEST_FOR_XML

    ORDER BY [TESTING!1!id!hide], Tag

    FOR XML EXPLICIT, ROOT('TEST')

    Here are the results:

    <TEST>

    <TESTING>

    <comment>acc</comment>

    <number>1</number>

    <boo>1</boo>

    <INTERNODE>

    <exe><![CDATA[acc]]></exe>

    </INTERNODE>

    </TESTING>

    <TESTING>

    <comment>BLAH</comment>

    <number>17</number>

    <boo>0</boo>

    <INTERNODE>

    <exe><![CDATA[BLAH]]></exe>

    </INTERNODE>

    </TESTING>

    <TESTING>

    <comment>&lt;'"&amp;&gt;</comment>

    <number>112</number>

    <boo>1</boo>

    <INTERNODE>

    <exe><![CDATA[<'"&>]]></exe>

    </INTERNODE>

    </TESTING>

    </TEST>

  • rayan98

    Mr or Mrs. 500

    Points: 577

    hey man , really thanks for the reply . as i m a newbie , sorry to ask silly questions 🙂

    can have a nested a for xml (path mode) with a for xml explicit mode 🙂

    cheers man

  • maroon-78

    SSC Eights!

    Points: 993

    Q from another noobie,

    is there a way to escape the '<' and '>' so that it doesn't get converted to &gt and &lt?

    I am using FOR XML PATH to combine comment fields. It is working great but some of the comments have portions framed by < and >. I would like to not have to do a replace after combining step.

    Thanks in advance for your help. Just point me at an example and I can figure it out.

    Jim

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply