XQuery in the SQL Server helps to query and extract data from XML documents. XQuery gives different approaches to get information from the XML document and the equivalent can be used on applying a data filter or where clause on XML elements as well.
SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column. SQL Server 2005 or later editions, allows using XML data typed input parameter in the stored procedure and supports using different XML functions to extract the information from XML nodes and vice versa with getting attributed in the XML data type column as well.
In this article, we will exercise with the Querying XML data type using XQuery to channel the information with the XML nodes and attributes with a few examples. Here, we have utilized one example table with sample data for a better explanation.
Sample XML:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<EmployeeDetails> <BusinessEntityID>3</BusinessEntityID> <NationalIDNumber>509647174</NationalIDNumber> <JobTitle>Engineering Manager</JobTitle> <BirthDate>1974-11-12</BirthDate> <MaritalStatus>M</MaritalStatus> <Gender>M</Gender> <StoreDetail> <Store> <AnnualSales>800000</AnnualSales> <AnnualRevenue>80000</AnnualRevenue> <BankName>Guardian Bank</BankName> <BusinessType>BM</BusinessType> <YearOpened>1987</YearOpened> <Specialty>Touring</Specialty> <SquareFeet>21000</SquareFeet> </Store> <Store> <AnnualSales>300000</AnnualSales> <AnnualRevenue>30000</AnnualRevenue> <BankName>International Bank</BankName> <BusinessType>BM</BusinessType> <YearOpened>1982</YearOpened> <Specialty>Road</Specialty> <SquareFeet>9000</SquareFeet> </Store> </StoreDetail> </EmployeeDetails> |
Fetching value from target XML node
The tabular format from an XML document with the bifurcation of XML node as the column name utilizing the SQL Server can be accomplished with the help of XQuery. XQuery will return the XML node value in the tabular model with a combination of nodes() and value() function on the XML column. nodes() function acknowledges XML node location as XPATH in the XML document and value() function returns XML node or attribute values which node is referenced inside the function with the data type.
XPATH inside the XQuery is utilized to find the XML node location in the XML column. XQuery returns the values from the specified XML node or attribute based on the parameters. XQuery accomplishes business logic with data manipulation on the XML node and attribute values inside the XML data type column with such requests.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName, X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e OUTER APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y) SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName, X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e CROSS APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y) |
Here, we have querying XML data type examples using XQuery with CROSS APPLY & OUTER APPLY. Both are very different use-case because CROSS APPLY will work as INNER JOIN with the nodes() function. If the path exists in the XML column, then the row will be fetched in the result set. If it does not exist, then that row will be filtered out. Well, OUTER APPLY will work as OUTER JOIN; if XPATH is not existed in the XML data typed column, then those rows will be fetched out with the NULL as XML element value in the result set.
In the above Query sample, we’ve just returned the XML element value from the XML document. But, a similar XQuery statement works in the WHERE clause as well. For example, return rows from the table in which employees are using “International bank” as “bank name“. As simple as, the user can apply a filter on the XML element value in the WHERE statement using subquery with taking query results inside query alias.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM( SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName, X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e CROSS APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y) )T WHERE ? = ? |
The above query statement works with fetching all values from the XML node then applies the filter on the same. But, this execution is resource consuming in terms of Query performance. But, How? And can we apply the direct filter to the target node?
Applying filter on XML node:
The nodes() function is much mature to separate the information to the row-column based tabular format from the XML node with the querying XML data type utilizing query() or value() function. Use of these functions progressively required when you are copying or moving a couple of nodes from the XML data type column. As above Query XML data type sample, Data is extricated from the XML with determining the XML node with the path. Now, we need just those rows whose XML information is satisfying with the WHERE condition with the XML node.
For example:
|
1 2 3 4 5 6 7 8 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(BankName)[1]', 'VARCHAR(20)') as BankName, X.Y.value('(AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e OUTER APPLY e.emp_xml.nodes('EmployeeDetails/StoreDetail/Store') as X(Y) WHERE X.Y.value('(AnnualRevenue)[1]', 'BIGINT') > 100000 |
Here, I have likewise used the extracted AnnualRevenue XML node value in the WHERE provision of above SELECT Query. Similar to the normal queries, we can utilize value() function in the WHERE clause. Users cannot use the alias of the XQuery statement in the WHERE provision. Indeed, on the off chance that we take that entire statement inside the inner Query with the table alias. However, it is not the right way for the performance of the query.
Here, dot notation is utilized to relate the query logic to the column name for the specific node. We parse the query() function with the XPATH statement advising the Querying XML data type to fetch the Annual Revenue for the employees; whose stores have an income greater than 100,000. Each slash in the XPATH proclamation represents a node index into the XML document.
This method takes the XQuery statement within the normal Query and returns a single value casting with data type like as mentioned in the XQuery statement. It is imperative to implement that XPATH refers to the whole XML document, which has been utilized in the query. It will fetch each node or attribute that matches the predefined node location, which may revert multiple elements, too, if multiple node locations exist with the same XPATH.
Handle namespace in XQuery :
An XML document can have a namespace in the hierarchy of the XML node. So either you can mention that namespace handle in the XPATH or specify the * to jump the namespace.
|
1 2 3 4 5 6 7 8 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(*:BankName)[1]', 'VARCHAR(20)') as BankName, X.Y.value('(*:AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(*:BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(*:Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e OUTER APPLY e.emp_xml.nodes('/*:EmployeeDetails/*:StoreDetail/*:Store') as X(Y) WHERE X.Y.value('(AnnualRevenue)[1]', 'BIGINT') > 100000 |
In the above code sample, we have used *: with each XML tag name in the XPATH.
EXIST with XQuery
The exist() XML function returns a BIT value (0 or 1) and it demonstrates that the value exists or not with the referenced arrangement. For example, XML element value is coordinating with the expression XPATH in the function then it returns 1 else 0. In this way, we ought to have the condition equals to 1 or 0. When a user wants to match the condition, then exist() equals 1 and for unequaled, it ought to be 0.
Here, this function returns a Boolean value depending upon the condition shown below:
|
1 2 3 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle FROM HumanResources.Employee e WHERE e.emp_xml.exist('(/*:EmployeeDetails/*:StoreDetail/*:Store/*:Specialty[.="Mountain"])')=1 |
Clients can place various conditions in the one XPATH proclamation. This exist() approach in the where provision consistently improves execution when we have a bigger XML with a lot of data to process.
EXISTS including XML node value:
As simple as user can use multiple combination of exist(), nodes(), value() and query() in a single SQL query. Everything depends on the query about the nature of the data to achieve better performance and business logic.
|
1 2 3 4 5 6 7 8 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(*:BankName)[1]', 'VARCHAR(30)') as BankName, X.Y.value('(*:AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(*:BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(*:Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e CROSS APPLY e.emp_xml.nodes('/*:EmployeeDetails/*:StoreDetail/*:Store') as X(Y) WHERE e.emp_xml.exist('(/*:EmployeeDetails/*:StoreDetail/*:Store/*:Specialty[.="Mountain"])')=1 |
A developer can implement any of the above approaches with the XML node to separate the information from the XML column or variable and by using the WHERE provision to put conditions on the same for filter out such certain values of the XML node or element.
Contains with EXIST() :
Contains within exist() function is similar to the LIKE operator in the WHERE clause. With the help of Contains, the user can look through any content in the destination XML node with the exist() function in the XML data type column.
|
1 2 3 4 5 6 7 8 |
SELECT e.BusinessEntityID, e.NationalIDNumber, e.OrganizationNode, e.JobTitle, X.Y.value('(*:BankName)[1]', 'VARCHAR(30)') as BankName, X.Y.value('(*:AnnualRevenue)[1]', 'VARCHAR(20)') as AnnualRevenue, X.Y.value('(*:BusinessType)[1]', 'VARCHAR(256)') as BusinessType, X.Y.value('(*:Specialty)[1]', 'VARCHAR(128)') as Specialty FROM HumanResources.Employee e CROSS APPLY e.emp_xml.nodes('/*:EmployeeDetails/*:StoreDetail/*:Store') as X(Y) WHERE e.emp_xml.exist('(/*:EmployeeDetails/*:StoreDetail/*:Store/*:BankName[contains(.,"Security")])')=1 |
Here, each BankName is scanned; Result set will return the rows, which contains the name with “security” in the target node in the “/*:EmployeeDetails/*:StoreDetail/*:Store/*:BankName” XPATH and the rest of rows will be skipped where “security” in the bank name node isn’t matching.
In straightforward words, WHERE condition on the XML node is extremely simple to utilize and execute it for the developer with good knowledge of the Querying XML data type using XQuery functions and well direction to accomplish it.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020