Extract Data from SQL Server XML Column

Introduction

Data extraction from SQL Server XML column is a little bit tricky unlike other columns of a SQL server table but not an impossible task. Here in this blog post shares code blocks in SQL to extract data value from a table that has an XML column (a column with XML Data type) and display each XML node or attributes in table format.

Getting Started

Let’s say you have an SQL server table named OrderMaster where you are storing the payment details in the XML format like below in a column that has XML Data type, and you want to query the xml column to extract data value.

 <Payments>  
      <Payment Mode = 'cash' Amount = '150' OrignalAmount = '150' CurrencyID = '3' CurrencyCode = 'RS' />  
      <Payment Mode = 'card' Amount = '120' CurrencyID = '3' CurrencyCode = 'RS' CardMonth = '3' CardYear = '2028' CardNumber = '7750' CardType = 'Visa' />  
 </Payments>  

SQL Server XML Column Data

The following SQL query extract XML column data and displays the data values in a table format in SQL server on the result window.

 DECLARE @Payments  
 TABLE(OrderNumber nvarchar(100), empXML XML)  
 INSERT INTO @Payments  
 SELECT OrderNumber,PaymentDetails FROM OrdersMaster  
 SELECT OrderNumber,  
 col.value('@Mode','VARCHAR(100)') AS PaymentMode,  
 col.value('@Amount','VARCHAR(100)') AS Amount,  
 col.value('@OrignalAmount','VARCHAR(100)') AS OrignalAmount,  
 col.value('@CurrencyCode','VARCHAR(100)') AS CurrencyCode,  
 col.value('@CardType','VARCHAR(100)') AS CardType  
 FROM @Payments   
 CROSS APPLY empXML.nodes('Payments/Payment') tab(col);  

Sql Query to Extract Data from XML Column

The above SQL Query, first declares a table variable to store the data including xml data value from the table which contains XML column then applies cross function on the XML Column to extract data from the xml attributes. The code is applicable to the XML that has nodes to contains data like below xml.

 <Payments>  
   <Payment>  
     <Mode>cash</Mode>  
     <Amount>150</Amount>  
     <OrignalAmount>150</OrignalAmount>  
     <CurrencyID>3</CurrencyID>  
     <CurrencyCode>RS</CurrencyCode>  
   </Payment>  
   <Payment> 
     <Mode>card</Mode>  
     <Amount>120</Amount>  
     <CurrencyID>3</CurrencyID>  
     <CurrencyCode>RS</CurrencyCode>  
     <CardMonth>3</CardMonth>  
     <CardYear>2028</CardYear>  
     <CardNumber>7750</CardNumber>  
     <CardType>Visa</CardType>  
   </Payment>  
 </Payments>  

SQL Server XML Data Values

Results of Extract Data From SQL Server on Result Window

 (1 row affected)  
 OrderNumber    PaymentMode  	Amount   OrignalAmount CurrencyCode CardType  
 ----------- --------------- ---------- ------------- ------------ --------  
 DG-849465384     cash      	150    		150      	AED     NULL  
 DG-849465384     card     	120    		NULL   		AED     Visa  
 (2 rows affected)  
 Completion time: 2023-06-30T13:35:36.9469831+05:30  

Reasons for storing XML data in SQL Server

Following are some of the reasons to use native XML features in SQL Server instead of managing your XML data in the file system:

1. You want to share, query, and modify your XML data in an efficient and transacted way. Fine-grained data access is important to your application. For example, you may want to extract some of the sections within an XML document, or you may want to insert a new section without replacing your whole document.
2. You have relational data and XML data and you want interoperability between both relational and XML data within your application.
3. You need language support for query and data modification for cross-domain applications.
4. You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schemas.
5. You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer.
6. You want SOAP, ADO.NET, and OLE DB access to XML data.
7. You want to use administrative functionality of the database server for managing your XML data. For example, this would be backup, recovery, and replication.

If none of these conditions is satisfied, it may be better to store your data as a non-XML, large object type, such as [n]varchar(max) or varbinary(max).

Thanks