SQL and XML shielded data

Question

I have a table with a mixture of shielded and unshielded XML. Of course, the data I needed 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, all the data in the Node tag is escaped. I can use the query to get the node data, but how can I convert it to XML in SQL so that it can be parsed and parsed? I am new to using XML in SQL, and I cannot find any examples of this.

thank

Answer 1

You have not provided enough information about your ultimate goal, but it will bring you closer. Fyi . You had two missing; as aftercomment>

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>

XML, XML.

Answer 2

HTML- UDF. :

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

, > <. , , .

UPDATE

@Cyberkiwi . , SQL Server, , .