Parse XML into SQL Server table and keep XHTML markup within XML element

Question

This issue is that there is xhtml markup inside the XML file (see Example_Code element) and when I run the XQuery it seems to strip out the markup and retain only the text. I would like to store the value of Example_Code in the table exactly as it is in the XML file . Many thanks for reading.

DECLARE @Xml XML = 
N'
<Demonstrative_Examples xmlns:xhtml="http://www.w3.org/1999/xhtml">>
    <Demonstrative_Example>
        <Intro_Text>This is the intro</Intro_Text>
        <Example_Code Nature="bad">Example 1.1</Example_Code>
        <Body_Text>Body 1.1</Body_Text>
        <Example_Code>   <xhtml:div>String sessionID = generateSessionId();<xhtml:br/>Cookie c = new Cookie("session_id", sessionID);<xhtml:br/>response.addCookie(c);</xhtml:div>
</Example_Code>
    </Demonstrative_Example>
    <Demonstrative_Example>
        <Intro_Text>This is the 2nd intro</Intro_Text>
        <Body_Text>Body 2.1</Body_Text>
        <Example_Code Nature="Good">Example 2.1</Example_Code>
    </Demonstrative_Example>
</Demonstrative_Examples>';

-- just to see
;WITH XMLNAMESPACES ('http://www.w3.org/1999/xhtml' as xhtml)  
SELECT @xml.query('<root>
{
    for $x in /Demonstrative_Examples/Demonstrative_Example
    let $id := count(/Demonstrative_Examples/Demonstrative_Example[. << $x[1]]) + 1
        for $y in $x/*[position() gt 1]
        let $pos := count($x/*[. << $y[1]]) + 1
        let $it := $x/*[local-name()="Intro_Text"]
        return <r id="{$id}" pos="{$pos - 1}" 
            Intro_Text="{$it}" 
            Body_Text="{$y[local-name()="Body_Text"]/text()}" 
            Example_Code="{$y[local-name()="Example_Code"]}"
            Nature="{$y[local-name()="Example_Code"]/@Nature}"></r>
}
</root>') AS xmldata;

-- real deal
;WITH rs AS
(
    SELECT @xml.query('<root>
    {
        for $x in /Demonstrative_Examples/Demonstrative_Example
        let $id := count(/Demonstrative_Examples/Demonstrative_Example[. << $x[1]]) + 1
            for $y in $x/*[position() gt 1]
            let $pos := count($x/*[. << $y[1]]) + 1
            let $it := $x/*[local-name()="Intro_Text"]
            return <r id="{$id}" pos="{$pos - 1}" 
                Intro_Text="{$it}" 
                Body_Text="{$y[local-name()="Body_Text"]/text()}" 
                Example_Code="{$y[local-name()="Example_Code"]/text()}"
                Nature="{$y[local-name()="Example_Code"]/@Nature}"></r>
    }
    </root>') AS xmldata
)
SELECT c.value('@id', 'INT') AS [ID] 
    , c.value('@pos', 'INT') AS [Order]
    , c.value('@Intro_Text', 'VARCHAR(30)') AS [Intro_Text]
    , c.value('@Body_Text', 'VARCHAR(30)') AS [Body_Text]
    , c.value('@Example_Code', 'VARCHAR(30)') AS [Example_Code]
    , c.value('@Nature', 'VARCHAR(30)') AS [Nature]
FROM rs CROSS APPLY xmldata.nodes('/root/r') AS t(c);

This is the resultset:

lptr · Accepted Answer · 2020-11-12 12:47:10Z

declare @Xml XML = 
N'
<Demonstrative_Examples xmlns:xhtml="http://www.w3.org/1999/xhtml">
    <Demonstrative_Example>
        <Intro_Text>This is the intro</Intro_Text>
        <Example_Code Nature="Bad">Example 1.1</Example_Code>
        <Body_Text>Body 1.1</Body_Text>
        <Example_Code><xhtml:div>String sessionID = generateSessionId();<xhtml:br/>Cookie c = new Cookie("session_id", sessionID);<xhtml:br/>response.addCookie(c);</xhtml:div></Example_Code>
    </Demonstrative_Example>
    <Demonstrative_Example>
        <Intro_Text>This is the 2nd intro</Intro_Text>
        <Body_Text>Body 2.1</Body_Text>
        <Example_Code Nature="Good">Example 2.1</Example_Code>
        <e4>e4</e4>        
        <e5>e5</e5>
        <e6>e6</e6>
        <e7>e7</e7>
        <e8>e8</e8>                        
        <Example_Code Nature="Ugly">examplecode position9 <xhtml:div>String sessionID = generateSessionId();<xhtml:br/>Cookie c = new Cookie("session_id", sessionID);<xhtml:br/>response.addCookie(c);</xhtml:div></Example_Code>
    </Demonstrative_Example>
</Demonstrative_Examples>';

select 
    s.example_order_id,
    s.position_in_example,
    case s.elementname when N'Intro_Text' then s.nodecontent end as IntroText,
    case s.elementname when N'Example_Code' then s.nodecontent end as ExampleCode,
    case s.elementname when N'Body_Text' then s.nodecontent end as BodyText,
    case s.elementname when N'Example_Code' then s.attributenature end as ExampleNature 
from
(
    select 
        dense_rank() over(order by d.ex) as example_order_id,
        row_number() over(partition by d.ex order by ex.el) as position_in_example,
        ex.el.value('local-name(.)[1]', 'nvarchar(100)') as elementname,
        ex.el.value('@Nature[1]', 'nvarchar(30)') as attributenature,
        cast(ex.el.query('node()') as nvarchar(max)) as nodecontent
    from @Xml.nodes('Demonstrative_Examples/Demonstrative_Example') as d(ex)
    outer apply d.ex.nodes('*') as ex(el)
) as s
where s.elementname in (N'Intro_Text', N'Example_Code', N'Body_Text');

Yitzhak Khabinsky · Accepted Answer · 2020-11-11 20:58:50Z

To preserve the <Example_Code> element value when it is XHTML/XML, you need to use CDATA section in the source XML. Please see below.

I made few additional modifications:

Commented out namespace declaration. It is not needed in this case.
Increased @Example_Code size as VARCHAR(MAX) to accommodate lengthy values.

SQL

DECLARE @Xml XML = 
N'<Demonstrative_Examples xmlns:xhtml="http://www.w3.org/1999/xhtml">
    <Demonstrative_Example>
        <Intro_Text>This is the intro</Intro_Text>
        <Example_Code Nature="bad">Example 1.1</Example_Code>
        <Body_Text>Body 1.1</Body_Text>
        <Example_Code><![CDATA[
            <xhtml:div>String sessionID = generateSessionId();<xhtml:br/>Cookie c = new Cookie("session_id", sessionID);<xhtml:br/>response.addCookie(c);</xhtml:div>
        ]]>
        </Example_Code>
    </Demonstrative_Example>
    <Demonstrative_Example>
        <Intro_Text>This is the 2nd intro</Intro_Text>
        <Body_Text>Body 2.1</Body_Text>
        <Example_Code Nature="Good">Example 2.1</Example_Code>
    </Demonstrative_Example>
</Demonstrative_Examples>';

-- just to see
--;WITH XMLNAMESPACES ('http://www.w3.org/1999/xhtml' as xhtml)  
SELECT @xml.query('<root>
{
    for $x in /Demonstrative_Examples/Demonstrative_Example
    let $id := count(/Demonstrative_Examples/Demonstrative_Example[. << $x[1]]) + 1
    let $it := $x/*[local-name()="Intro_Text"]
        for $y in $x/*[position() gt 1]
        let $pos := count($x/*[. << $y[1]]) + 1
        return <r id="{$id}" pos="{$pos - 1}" 
            Intro_Text="{$it}" 
            Body_Text="{$y[local-name()="Body_Text"]/text()}" 
            Example_Code="{$y[local-name()="Example_Code"]}"
            Nature="{$y[local-name()="Example_Code"]/@Nature}"></r>
}
</root>') AS xmldata;

-- real deal
;WITH rs AS
(
    SELECT @xml.query('<root>
    {
        for $x in /Demonstrative_Examples/Demonstrative_Example
        let $id := count(/Demonstrative_Examples/Demonstrative_Example[. << $x[1]]) + 1
        let $it := $x/*[local-name()="Intro_Text"]
            for $y in $x/*[position() gt 1]
            let $pos := count($x/*[. << $y[1]]) + 1
            return <r id="{$id}" pos="{$pos - 1}" 
                Intro_Text="{$it}" 
                Body_Text="{$y[local-name()="Body_Text"]/text()}" 
                Example_Code="{$y[local-name()="Example_Code"]/text()}"
                Nature="{$y[local-name()="Example_Code"]/@Nature}"></r>
    }
    </root>') AS xmldata
)
SELECT c.value('@id', 'INT') AS [ID] 
    , c.value('@pos', 'INT') AS [Order]
    , c.value('@Intro_Text', 'VARCHAR(30)') AS [Intro_Text]
    , c.value('@Body_Text', 'VARCHAR(30)') AS [Body_Text]
    , c.value('@Example_Code', 'VARCHAR(MAX)') AS [Example_Code]
    , c.value('@Nature', 'VARCHAR(30)') AS [Nature]
FROM rs CROSS APPLY xmldata.nodes('/root/r') AS t(c);

Collectives™ on Stack Overflow

Parse XML into SQL Server table and keep XHTML markup within XML element

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related