SQL Server Query XML in Nvarchar(max) Field?
I have XML stored in an nvarchar(max) field. I realize there is an XML data type, but in this case it开发者_如何学JAVA is not stored that way. Let's say the XML is structured like the following:
<root>
<hdr>
<name>aj</name>
</hdr>
<dtls>
<dtl>
<price>1</price>
</dtl>
<dtl>
<price>7</price>
</dtl>
<dtl>
<price>3</price>
</dtl>
</dtls>
</root>
What I am trying to do is get the count of detail (dtl) nodes that exist for record. I am sure this is possible with xpath/xquery, I am just not exactly sure how.
Try this:
SELECT CAST(<YOUR_XML_COLUMN> AS XML).query('count(//dtl)')
FROM <YOUR_TABLE>
e.g:
DECLARE @x NVARCHAR(MAX)
SET @x = '<root> <hdr> <name>aj</name> </hdr> <dtls> <dtl> <price>1</price> </dtl> <dtl> <price>7</price> </dtl> <dtl> <price>3</price> </dtl> </dtls> </root>'
SELECT CAST(@x AS XML).query('count(//dtl)')
精彩评论