Partager via

XML Features - XQuery

  • Article

----------------
--XML Datatype--
----------------
/*
The xml datatype can be queried using XQuery.
using one of several methods on it:
.query, .value, .nodes, .modify and .exists
.XQuery defines the FLWOR iteration syntax.
FLWOR is the acronym of for, let, where, order by, and return.
In SQL Server 2005, let is unsupported.
This syntax can be used to replace OPENXML in SQL 2000
*/

-----------------
--.query Method--
-----------------
/*
The query method allows reformatting of a document into other formats
It could be considered a little like XSLT
*/
DECLARE @doc xml
SET @doc =
'<people>
<person>Martin</person>
<person>Simon</person>
<person>Mark</person>
</people>'
SELECT @doc.query('
for $p in /people/person/text()
return
<name>
{$p}
</name>
') AS queryresults
/*
Returns:
queryresults
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<name>Martin</name><name>Simon</name><name>Mark</name>
(1 row(s) affected)
*/

-----------------
--.value Method--
-----------------
/*
The value method allows a single value to be extracted from an xml document
*/
DECLARE @doc xml, @count int
SET @doc =
'<people>
<person>Martin</person>
<person>Simon</person>
<person>Mark</person>
</people>'
SELECT @count = @doc.value('count(/people/person)', 'int')
SELECT @count AS [count]
/*
Returns:
count
-----------
3
(1 row(s) affected)
*/

----------------------
--Using sql:variable--
----------------------
/*
It is not possible to construct a custom xquery string
However, it is possible to pass in a sql variables as parameters
to the XQuery statement.
NOTE: SQL must be sure that a single value will be returned,
hence the final [1], even though the statement
/people/person[sql:variable("@pos")]/text()
would only ever return 1 value
*/
DECLARE @doc xml, @pos int
SET @doc =
'<people>
<person>Martin</person>
<person>Simon</person>
<person>Mark</person>
</people>'
SELECT @pos = 2
SELECT @doc.value('(/people/person[sql:variable("@pos")]/text())[1]', 'nvarchar(256)') AS SecondName
/*
Returns:
SecondName
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Simon
(1 row(s) affected)
*/

----------------
--Using .nodes--
----------------
/*
.nodes can be used to shred xml into a table
In 2000 we had to use OPENXML
*/
DECLARE @doc xml
SET @doc =
'<people>
<person>Martin</person>
<person>Simon</person>
<person>Mark</person>
</people>'
SELECT
PeopleTable.PersonColumn.query('text()') AS [Name]
FROM
@doc.nodes('people/person') AS PeopleTable(PersonColumn);
/*
Returns:
Name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Martin
Simon
Mark
(3 row(s) affected)
*/

------------------------
--Other XQuery methods--
------------------------
/*
.modify which allows update/delete/insert
.exists test if node exists
*/

Comments

  • Anonymous
    June 15, 2013
    Xquery & XML methods are very powerful handling XML in SQL Server,thanks for the post ...