In addition to FOR XML, SQL Server allows programmer to extract data stored in variables or columns with the XML data type by using XQuery. XQuery is a language that uses a set of statements and functions provided by the XML data type to extract data. As compared to the FOR XML clause of the SELECT statement, the XQuery statements allow you to extract specific parts of the XML data.
Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:
The XQuery statements also use the following functions provided by the XML data type:
Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.
To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.
To perform this task, the database developer can create the following query:
SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL
Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.
For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:
SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL
Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:
SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails
Retrieve XML data from DataSet
Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:
- For: Used to iterate through a set of nodes at the same level as in an XML document.
- Let: Used to declare variables and assign values.
- Order by: Used to specify a sequence.
- Where: Used to specify criteria for the data to be extracted.
- Return: Used to specify the XML returned from a statement.
The XQuery statements also use the following functions provided by the XML data type:
Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.
To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.
To perform this task, the database developer can create the following query:
SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL
Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.
For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:
SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL
Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:
SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails
Retrieve XML data from DataSet