Friday, March 27, 2015

How do I query this xml?

Why is this not returning any results?


Xml



<Results>
<search>
<criteria field="city" value="New York" />
<criteria field="state" value="NY" />
</search>
<result max="22629" min="250"/>
</Results>



Query



SELECT
x.XmlId,
b.value('@city', 'nvarchar(50)') as [City]
c.value('@max', 'nvarchar(50)') as [Max]

From
XmlTable As x

Cross Apply x.XmlColumn.nodes('Results') a(a)
Cross Apply a.nodes('search/criteria') b(b)
Cross Apply a.nodes('result') c(c)

Where x.XmlId = 1


No comments:

Post a Comment