Query XML fields in MSSQL using TSQL and XQuery. Getting Counts and Values.
Finding the number of occurrences of an element in XML. This query returns the number of occurences of the title element under an article element from the MyTable.XmlField column.
SELECT XmlField.value('count(//article/title)', 'int')
FROM MyTable
Finding the number of occurrences of an attribute in XML. This query returns the number of occurences of the image attribute of a title element under an article element from the
MyTable.XmlField column.
SELECT XmlField.value('count(//article/title[@image])', 'int')
FROM MyTable
Retrieving a string element. This query returns the value of the first introduction element under the article element of the MyTable.XmlField column.
SELECT XmlField.value('(//article/introduction)[1]', 'nvarchar(max)')
FROM MyTable
Retrieving data of many child elements. This query returns all the values of the section elements from the MyTable.XmlField column.
SELECT XmlField.CAST(BodyXml.query('for $x in //section return data($x)') as nvarchar(max))
FROM MyTable
Written By: mycodeshare on March 24, 2009 at 10:20
Submit a comment, suggestion, or additional information about this snippet
If you login or register,
you'll be able to post a comment or provide feedback about this snippet.
Comments
Good information. Not a lot of articles about XQuery currently out there.
Here is a sample of how you would do a count of attributes, based on a specific attribute value.