The best way to explain XQuery is to say that XQuery is to XML what SQL is to database tables.
-
XQuery was designed to query XML data.
-
XQuery is also known as XML Query.
-
XQuery is the language for querying XML data
-
XQuery for XML is like SQL for databases
-
XQuery is built on XPath expressions
-
XQuery is supported by all the major database engines (IBM, Oracle, Microsoft, etc.)
-
XQuery is a W3C Recommendation
XQuery can be used to:
-
Extract information to use in a Web Service
-
Generate summary reports
-
Transform XML data to XHTML
-
Search Web documents for relevant information
Example 1
DECLARE @BookXML XML
SET @BookXML=
'
<BOOKS>
<BOOK>
<Name>C#.NET</Name>
<Author> James </Author>
<Author> Smith </Author>
</BOOK>
<BOOK>
<Name> ASP.NET</Name>
<Author> Mohammed </Author>
<Author> Akmal </Author>
</BOOK>
</BOOKS>
'
-- xml.exist() uses XQuery input to return 0/1/NULL
-- It returns 0 if no elements match
-- It returns 1 if elements match
SELECT @BookXML.exist('BOOKS/BOOK')
--xml.value() accepts an XQuery as input and returns an SQL Server Scalar type.
-- This function requires two arguments
SELECT @BookXML.value('BOOKS[1]/BOOK[1]/Name[1]','nvarchar(max)')
SELECT @BookXML.value('BOOKS[1]/BOOK[2]/Name[1]','nvarchar(max)')
-- xml.query() accepts an XQuery as input and returns an XML datatype
-- as output(XML Format)
SELECT @BookXML.query('BOOKS[1]/BOOK[1]/Name[1]')
--xml.query() with Variable
SELECT @BookXML.query(' for $B in BOOKS[1]/BOOK[1]/Name[1] return($B)')
Example 2
CREATE DATABASE MyXQueryDB;
USE MyXQueryDB;
CREATE Table BookDetailsTable
(
Author_ID Integer PRIMARY KEY IDENTITY,
Author_Name NVARCHAR(50),
Author_Country NVARCHAR(25),
Books_XML XML Not Null
);
Insert INTO BookDetailsTable VALUES
(
'Stephen Forte', 'USA',
'<BOOKS>
<BOOK name="Writing Secure Code for ASP .NET "/>
<BOOK name="Using XQuery to Query and Manipulate XML Data in SQL Server 2005"/>
<BOOK name="SQL Server and Oracle Working Together"/>
<BOOK name="Protecting against SQL Injection Attacks "/>
</BOOKS> '
);
INSERT INTO BookDetailsTable VALUES
(
'Richard Campbell', 'Japan',
'<BOOKS>
<BOOK name="ADO.NET"/>
<BOOK name="C# Complte Reference"/>
<BOOK name="ASP.NET for Developers"/>
</BOOKS> '
);
INSERT INTO BookDetailsTable VALUES
(
'Stephen Forte', 'USA',
'<BOOKS>
<BOOK name="Writing Secure Code for ASP .NET "/>
<BOOK name="Using XQuery to Query and Manipulate XML Data in SQL Server 2005"/>
<BOOK name="SQL Server and Oracle Working Together"/>
<BOOK name="Protecting against SQL Injection Attacks "/>
<BOOK1 name="Exception Handling in C# "/>
</BOOKS>'
);
Query 1:
SELECT * FROM BookDetailsTable
Query 2:
-- xml.exist() uses XQuery input to return 0/1/NULL
-- It returns 0 if no elements match
-- It returns 1 if elements match
SELECT Books_XML.exist('/BOOKS/BOOK1')
FROM BookDetailsTable
Query 3:
--xml.value() accepts an XQuery as input and returns an SQL Server Scalar type.
-- This function requires two arguments
SELECT Author_ID, Author_Name, Author_Country,
Books_XML.value('/BOOKS[1]/BOOK[1]/@name', 'varchar(40)')
AS Sessions
FROM BookDetailsTable WHERE Author_Country ='USA'
Query 4:
-- xml.query() accepts an XQuery as input and returns an XML datatype
-- as output(XML Format)
SELECT Author_ID, Author_Name, Author_Country,
Books_XML.query('BOOKS[1]/BOOK[1]')
AS Sessions
FROM BookDetailsTable WHERE Author_Country ='USA'
Query 5:
--xml.query() with Variable
SELECT Author_ID, Author_Name, Author_Country,
Books_XML.query('for $b in BOOKS/BOOK return ($b) ') AS BOOK
FROM BookDetailsTable
Query 6:
--xml.query() with Variable and Orderby
SELECT Author_ID, Author_Name, Author_Country,
Books_XML.query('for $b in BOOKS/BOOK order by $b/@name return ($b) ') AS BOOK
FROM BookDetailsTable
WHERE Author_Country ='USA'
Recent Comments