Querying encoded XML within unencoded XML column in SQL Server 2012

Question

I have table with an XML column which looks like the following. The XML column has some encoded XML inside unencoded XML.

OrderID    UserDefXML
      1    <Order><OrderDetail><LineItem><ItemName>XYZ</ItemName><Custominfo> &lt;PrimaryName&gt;STACKOVERFLOW &lt;/PrimaryName&gt;</Custominfo></LineItem></OrderDetail></Order>

I need to extract all the rows which has PrimaryName LIKE 'STACK%'. What should the query be like for this?

har07 · Accepted Answer · 2015-11-19 02:30:18Z

2

You can convert the encoded XML string -the content of Custominfo element- to XML data type first, for example :

SELECT t.*, x.lineItem.value('Custominfo[1]','varchar(max)') 'CustomInfo'
FROM YourTableName t
CROSS APPLY t.UserDefXML.nodes('/Order/OrderDetail/LineItem') as x(lineItem)
WHERE 
  CONVERT(XML, x.lineItem.value('Custominfo[1]','varchar(max)')).value('PrimaryName[1]','varchar(max)') 
  LIKE 'STACK%'

Sqlfiddle Demo

Output :

edited Nov 19, 2015 at 2:30

answered Nov 19, 2015 at 2:14

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying encoded XML within unencoded XML column in SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related