CODEWITHSUNDEEP
sqlsql-serverxml
Adam Rosen
Adam Rosen

Reputation: 45

SQL and escaped XML data

I have a table with a mix of escaped and non-escaped XML. Of course, the data I need is escaped. For example, I have:

<Root>
    <InternalData>
         <Node>
              &lt;ArrayOfComment&gt;
                 &lt;Comment&gt
                    &lt;SequenceNo&gt;1&lt;/SequenceNo&gt;
                    &lt;IsDeleted&gt;false&lt;/IsDeleted&gt;
                    &lt;TakenByCode&gt;397&lt;/TakenByCode&gt;
                 &lt;/Comment&gt
              &lt;/ArrayOfComment&gt;
         </Node>
    </InternalData>
</Root>

As you can see, the data in the Node tag is all escaped. I can use a query to obtain the Node data, but how can I convert it to XML in SQL so that it can be parsed and broken up? I'm pretty new to using XML in SQL, and I can't seem to find any examples of this.

Thanks

Upvotes: 4

Views: 5673

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85126

Your best bet might be to look into a HTML Decoding UDF. I did a quick search and found this one:

http://www.andreabertolotto.net/Articles/HTMLDecodeUDF.aspx

You may want to modify it so it only decodes &gt; and &lt;. The one above seems to go above and beyond your needs.

UPDATE

@Cyberkiwi's solution seems to be a bit cleaner. I will leave this up in case the version of SQL Server you are running doesn't support his solution.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

You have not given enough information about your end goal, but this will get you very close. FYI - You had two missing ; both after comment&gt

declare @xml xml
set @xml = '
<Root>
    <InternalData>
         <Node>
              &lt;ArrayOfComment&gt;
                 &lt;Comment&gt;
                    &lt;SequenceNo&gt;1&lt;/SequenceNo&gt;
                    &lt;IsDeleted&gt;false&lt;/IsDeleted&gt;
                    &lt;TakenByCode&gt;397&lt;/TakenByCode&gt;
                 &lt;/Comment&gt;
              &lt;/ArrayOfComment&gt;
         </Node>
    </InternalData>
</Root>
'

select convert(xml, n.c.value('.', 'varchar(max)'))
from @xml.nodes('Root/InternalData/Node/text()') n(c)

Output

<ArrayOfComment>
  <Comment>
    <SequenceNo>1</SequenceNo>
    <IsDeleted>false</IsDeleted>
    <TakenByCode>397</TakenByCode>
  </Comment>
</ArrayOfComment>

The result is an XML column that you can put into a variable or cross-apply into directly to get data from the XML fragment.

Upvotes: 5

Related Questions