T-SQL: Parse an XML value

What?
This took me a while to find so I've posted an article below detailing how to parse or extract values from a string containing XML code.

Why?
I'm working with a system which stores XML strings in a database and rather than a separate file, it stores these in a row.

How?
Assuming the following data exists in the column "Event_XML" value of the table "XML_EVENTS":
<?xml version="1.0" encoding="UTF-8"?>
<STAFF xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <EMPLOYEE_NUMBER>123456</EMPLOYEE_NUMBER>
      <TITLE>Mr</TITLE>
      <INITIALS>J</INITIALS>
      <FORENAME>JOEL</FORENAME>
      <SURNAME>LIPMAN</SURNAME>
      <GENDER>MALE</GENDER>
      <DATE_OF_BIRTH>1990-01-01</DATE_OF_BIRTH>
      <DEPARTMENT_DESC>SUPER SUPPORT TEAM</DEPARTMENT_DESC>
      <JOB_TITLE_DESC>GENERAL DOGSBODY</JOB_TITLE_DESC>
      <ORIGINAL_DATE_JOINED>2014-03-01</ORIGINAL_DATE_JOINED>
      <CURRENT_EMPLOYEE financialyear="2014">Y</CURRENT_EMPLOYEE>
      <INTERNAL_EMAIL xsi:nil="true" />
      <CHANGE_TYPE>INSERT</CHANGE_TYPE>
      <CHANGE_DATE>2014-03-27</CHANGE_DATE>
</STAFF>

Extracting the Gender:
We're going to extract the value in between the opening and closing XML tags:
copyraw
-- Using .query()
SELECT 
	CAST(CAST(Event_XML AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender
FROM 
	[dbo].[XML_EVENTS] 

-- Returns column GENDER for every record/row (eg. "Male")
  1.  -- Using .query() 
  2.  SELECT 
  3.      CAST(CAST(Event_XML AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender 
  4.  FROM 
  5.      [dbo].[XML_EVENTS] 
  6.   
  7.  -- Returns column GENDER for every record/row (eg. "Male") 
Or
copyraw
-- Using .value()
SELECT 
	CAST(Event_XML AS XML).value('data(/STAFF/GENDER)[1]','VARCHAR(10)') AS Gender
FROM 
	[dbo].[XML_EVENTS] 

-- Returns column GENDER for every record/row (eg. "Male")
  1.  -- Using .value() 
  2.  SELECT 
  3.      CAST(Event_XML AS XML).value('data(/STAFF/GENDER)[1]','VARCHAR(10)') AS Gender 
  4.  FROM 
  5.      [dbo].[XML_EVENTS] 
  6.   
  7.  -- Returns column GENDER for every record/row (eg. "Male") 

Extracting the attribute:
Here we want to extract an attribute value
copyraw
SELECT 
	CAST(CAST(Event_XML AS XML).query('data(/STAFF/CURRENT_EMPLOYEE/@financialyear)') AS INT(4)) AS FinancialYear
FROM 
	[dbo].[XML_EVENTS] 

-- Returns attribute "financialyear" of CURRENT_EMPLOYEE (eg. "2014")
  1.  SELECT 
  2.      CAST(CAST(Event_XML AS XML).query('data(/STAFF/CURRENT_EMPLOYEE/@financialyear)') AS INT(4)) AS FinancialYear 
  3.  FROM 
  4.      [dbo].[XML_EVENTS] 
  5.   
  6.  -- Returns attribute "financialyear" of CURRENT_EMPLOYEE (eg. "2014") 

In a WHERE clause
To select records matching a value for the XML field:
copyraw
SELECT 
        *
FROM 
        [dbo].[XML_EVENTS] 
WHERE
        CAST(Event_XML AS XML).value('(/STAFF/GENDER="MALE")[1]','VARCHAR(10)')='TRUE' AS Gender

-- Returns records where GENDER = "Male"
  1.  SELECT 
  2.          * 
  3.  FROM 
  4.          [dbo].[XML_EVENTS] 
  5.  WHERE 
  6.          CAST(Event_XML AS XML).value('(/STAFF/GENDER="MALE")[1]','VARCHAR(10)')='TRUE' AS Gender 
  7.   
  8.  -- Returns records where GENDER = "Male" 


More Examples (Note that Event_XML is of datatype XML in the following examples):
copyraw
-- exist() returns 1 or 0 if node exists or not respectively
SELECT Event_XML.exist('/STAFF/EMPLOYEE_NUMBER') 
FROM [dbo].[XML_EVENTS]

-- query() returns typed XML
SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER') 
FROM [dbo].[XML_EVENTS]

-- value() returns single value, specify data type as second parameter
SELECT Event_XML.value('(/STAFF/EMPLOYEE_NUMBER/@name)[1]','varchar(20)') 
FROM [dbo].[XML_EVENTS]
  1.  -- exist() returns 1 or 0 if node exists or not respectively 
  2.  SELECT Event_XML.exist('/STAFF/EMPLOYEE_NUMBER') 
  3.  FROM [dbo].[XML_EVENTS] 
  4.   
  5.  -- query() returns typed XML 
  6.  SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER') 
  7.  FROM [dbo].[XML_EVENTS] 
  8.   
  9.  -- value() returns single value, specify data type as second parameter 
  10.  SELECT Event_XML.value('(/STAFF/EMPLOYEE_NUMBER/@name)[1]','varchar(20)') 
  11.  FROM [dbo].[XML_EVENTS] 

Issues:
  • XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *' This is because more than one record was returned so you need to surround with brackets and append a [1] as per the above example.

Retrieving nodes
copyraw
-- to get all below nodes from root node - STAFF
SELECT Event_XML.query('/STAFF') 
FROM [dbo].[XML_EVENTS]

-- to get all element nodes named 'EMPLOYEE_NUMBER'
SELECT Event_XML.query('//EMPLOYEE_NUMBER') 
FROM [dbo].[XML_EVENTS]

-- to get employee node with ID = 1
SELECT Event_XML.query('//EMPLOYEE_NUMBER[@ID = 1]') 
FROM [dbo].[XML_EVENTS]

-- to get employee node at position = 1 from root node
SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[position()=1]') 
FROM [dbo].[XML_EVENTS]

-- to get employee node at last position  from root node
SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[last()]') 
FROM [dbo].[XML_EVENTS]

-- to return unknown nodes from root node - STAFF
SELECT Event_XML.query('/STAFF/*') 
FROM [dbo].[XML_EVENTS]

-- to return unknown nodes from XML column - Event_XML
SELECT Event_XML.query('//* ') 
FROM [dbo].[XML_EVENTS]
  1.  -- to get all below nodes from root node - STAFF 
  2.  SELECT Event_XML.query('/STAFF') 
  3.  FROM [dbo].[XML_EVENTS] 
  4.   
  5.  -- to get all element nodes named 'EMPLOYEE_NUMBER' 
  6.  SELECT Event_XML.query('//EMPLOYEE_NUMBER') 
  7.  FROM [dbo].[XML_EVENTS] 
  8.   
  9.  -- to get employee node with ID = 
  10.  SELECT Event_XML.query('//EMPLOYEE_NUMBER[@ID = 1]') 
  11.  FROM [dbo].[XML_EVENTS] 
  12.   
  13.  -- to get employee node at position = 1 from root node 
  14.  SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[position()=1]') 
  15.  FROM [dbo].[XML_EVENTS] 
  16.   
  17.  -- to get employee node at last position  from root node 
  18.  SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[last()]') 
  19.  FROM [dbo].[XML_EVENTS] 
  20.   
  21.  -- to return unknown nodes from root node - STAFF 
  22.  SELECT Event_XML.query('/STAFF/*') 
  23.  FROM [dbo].[XML_EVENTS] 
  24.   
  25.  -- to return unknown nodes from XML column - Event_XML 
  26.  SELECT Event_XML.query('//* ') 
  27.  FROM [dbo].[XML_EVENTS] 

Parent/Child Node Names
copyraw
-- to get name of parent node -- returns STAFF
SELECT 
	(
		SELECT 
			c.value('local-name(.)', 'nvarchar(50)')
		FROM
			Event_XML.nodes('/*') AS r(c)
	) AS RootParent
FROM [dbo].[XML_EVENTS]


-- to get name of immediate child node -- one level down
SELECT 
	(
		SELECT 
			c.value('local-name(.)', 'nvarchar(50)')
		FROM
			Event_XML.nodes('/*/*') AS r(c)
	) AS RootChild
FROM [dbo].[XML_EVENTS]


-- to get name of child node containing string "SURNAME"
SELECT 
	(
		SELECT 
			c.value('local-name(.)', 'nvarchar(50)')
		FROM
			Event_XML.nodes('/*/*[contains(local-name(.),"SURNAME")][1]') AS r(c)
			-- added [1] to only get 1 result returned
	) AS Node_Surname
FROM [dbo].[XML_EVENTS]
  1.  -- to get name of parent node -- returns STAFF 
  2.  SELECT 
  3.      ( 
  4.          SELECT 
  5.              c.value('local-name(.)', 'nvarchar(50)') 
  6.          FROM 
  7.              Event_XML.nodes('/*') AS r(c) 
  8.      ) AS RootParent 
  9.  FROM [dbo].[XML_EVENTS] 
  10.   
  11.   
  12.  -- to get name of immediate child node -- one level down 
  13.  SELECT 
  14.      ( 
  15.          SELECT 
  16.              c.value('local-name(.)', 'nvarchar(50)') 
  17.          FROM 
  18.              Event_XML.nodes('/*/*') AS r(c) 
  19.      ) AS RootChild 
  20.  FROM [dbo].[XML_EVENTS] 
  21.   
  22.   
  23.  -- to get name of child node containing string "SURNAME" 
  24.  SELECT 
  25.      ( 
  26.          SELECT 
  27.              c.value('local-name(.)', 'nvarchar(50)') 
  28.          FROM 
  29.              Event_XML.nodes('/*/*[contains(local-name(.),"SURNAME")][1]') AS r(c) 
  30.              -- added [1] to only get 1 result returned 
  31.      ) AS Node_Surname 
  32.  FROM [dbo].[XML_EVENTS] 

Search if Node or value contains string:
copyraw
-- exist() returns 1 or 0 if a node name exists containing "NAME" or not respectively
SELECT Event_XML.exist('(/*/*[1][contains(local-name(.),"NAME")])')  
FROM [dbo].[XML_EVENTS]

-- query() returns XML branch if the node name contains string "NAME" -- returns <SURNAME>LIPMAN</SURNAME>
SELECT Event_XML.query('(/*/*[1][contains(local-name(.),"NAME")])') 
FROM [dbo].[XML_EVENTS]

-- query() returns XML branch if the node value contains string "MAN" -- returns <SURNAME>LIPMAN</SURNAME>
SELECT Event_XML.query('(/*/*[1][contains(.,"MAN")])') 
FROM [dbo].[XML_EVENTS]

-- value() returns value of node if the node name contains string "NAME" -- returns LIPMAN
SELECT Event_XML.value('(/*/*[1][contains(local-name(.),"NAME")])[1]','NVARCHAR(50)') 
FROM [dbo].[XML_EVENTS]

-- value() returns value of node if the node value contains string "MAN" -- returns LIPMAN
SELECT Event_XML.value('(/*/*[1][contains(.,"MAN")])[1]','NVARCHAR(50)') 
FROM [dbo].[XML_EVENTS]
  1.  -- exist() returns 1 or 0 if a node name exists containing "NAME" or not respectively 
  2.  SELECT Event_XML.exist('(/*/*[1][contains(local-name(.),"NAME")])') 
  3.  FROM [dbo].[XML_EVENTS] 
  4.   
  5.  -- query() returns XML branch if the node name contains string "NAME" -- returns <SURNAME>LIPMAN</SURNAME> 
  6.  SELECT Event_XML.query('(/*/*[1][contains(local-name(.),"NAME")])') 
  7.  FROM [dbo].[XML_EVENTS] 
  8.   
  9.  -- query() returns XML branch if the node value contains string "MAN" -- returns <SURNAME>LIPMAN</SURNAME> 
  10.  SELECT Event_XML.query('(/*/*[1][contains(.,"MAN")])') 
  11.  FROM [dbo].[XML_EVENTS] 
  12.   
  13.  -- value() returns value of node if the node name contains string "NAME" -- returns LIPMAN 
  14.  SELECT Event_XML.value('(/*/*[1][contains(local-name(.),"NAME")])[1]','NVARCHAR(50)') 
  15.  FROM [dbo].[XML_EVENTS] 
  16.   
  17.  -- value() returns value of node if the node value contains string "MAN" -- returns LIPMAN 
  18.  SELECT Event_XML.value('(/*/*[1][contains(.,"MAN")])[1]','NVARCHAR(50)') 
  19.  FROM [dbo].[XML_EVENTS] 

Looping
copyraw
-- From resultset of $i ,assigning it to variable $j using let clause,
-- Getting top two employees by using where & order by clause.

SELECT Event_XML.query('for $i in /STAFF/student
let $j:= $i
where ($j/@ID)
  1.  -- From resultset of $i ,assigning it to variable $j using let clause, 
  2.  -- Getting top two employees by using where & order by clause. 
  3.   
  4.  SELECT Event_XML.query('for $i in /STAFF/student 
  5.  let $j:$i 
  6.  where ($j/@ID) 
Category: Transact-SQL :: Article: 555

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.