In this tutorial, we will discuss how to read data from an XML column using a SQL Query.
Read xml data from a variable
In the first example, we are going to read data from a variable.
declare @xmlcol xml;
Set @xmlcol = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
.value
There are certain methods that SQL Server provides that can be used to read data of the XML data type and the first function that we are going to use is the .value() method.
Select @xmlcol.value('(/Root/ProductDescription/@ProductID)[1]', 'int')
1
Select @xmlcol.value('(/Root/ProductDescription/Features/Warranty)[1]' , 'varchar(100)')
1 year parts and labor
SQL server has a Singleton rule[1] which means that if you have repeating instances of node or attribute values, the first instance needs to be picked up.
Read data from xml column in a table
Next we are going to use the AdventureWorks database of Microsoft to read data from an xml column in a database table.
Select CatalogDescription.value('declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";(/PD:ProductDescription/@ProductModelID)[1]', 'int')from Production.ProductModel WHERE CatalogDescription is not NULL AND catalogDescription.exist('declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription";declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelWarrAndMain";/PD:ProductDescription/PD:Features/wm:Warranty') = 1
19
23
25
28
34
35
We have used another function .exist() in the above query to filter the data. The .exist() function does not need a singleton rule and hence does not need [1].
We will discuss more on filtering the data later in the tutorial.
WITH XMLNAMESPACES
Declaring these namespaces and defining them again and again and the query is a bit tedious.
So instead of defining the namespaces multiple times, we can use the WITH XMLNAMESPACES clause.
;WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' as PD,'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' as wm)
Select CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int')
from Production.ProductModel
WHERE CatalogDescription is not NULL
AND catalogDescription.exist('/PD:ProductDescription/PD:Features/wm:Warranty') = 1
19
23
25
28
34
35
Filtering the data
We can use the .exist() method to filter the result based on existence of a particular xml attribute and value pair.
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' as ns)
Select Demographics.value('(/ns:IndividualSurvey/ns:TotalPurchaseYTD)[1]', 'varchar(50)')
FROM Person.Person
WHERE Demographics.exist('(/ns:IndividualSurvey/ns:Gender[.="M"])') = 1
In the above query, only those Demographic values will be returned for which Gender is set to 'M'.
Sometimes there can be spaces in the values in data. In the above query, if the value would have been 'M ', the record would not have been filtered to be returned in the output.
So, a better way is to use contains which looks for the occurrence of the specified substring in the data.
WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' as ns)
Select Demographics.value('(/ns:IndividualSurvey/ns:TotalPurchaseYTD)[1]', 'varchar(50)')
FROM Person.Person
WHERE Demographics.exist('(/ns:IndividualSurvey/ns:Gender[contains(.,"M")])') = 1
You can find more information about these functions in the Microsoft Learn website here -
https://learn.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver16
Must Do Data Analytics Certifications
Google Data Analytics Professional Certificate Google Advanced Data Analytics Professional Certificate