How to Read xml file from sql server?

March 17, 2016

Blogger Image

Rajapandian R

Module Lead

There might be a requirement to read the xml file from sql server and process the values. In this blog we’ll see how to read the node values of xml file from sql server.

My default xml file will be in below format:


<?xml version="1.0" standalone="yes" ?>
<Price>
  <Rate>
     <ITEMNMBR>100</ITEMNMBR> 
     <ITEMDESC>Audio Cassette</ITEMDESC> 
     <QTY>1</QTY> 
     <TPRICE>50</TPRICE> 
  </Rate>
  <Rate>
     <ITEMNMBR>101</ITEMNMBR> 
     <ITEMDESC>Video Cassette</ITEMDESC> 
     <QTY>1</QTY> 
     <TPRICE>100</TPRICE> 
  </Rate>
</Price>


Using "xp_cmdshell" command, we can read the contents of xml file like below. Finally the variable @FileContents will have the contents of xml file.

DECLARE @FilePathvarchar(255)
DECLARE @xCmd VARCHAR(255)
DECLARE @FileContents VARCHAR(MAX)

CREATE TABLE #temp(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(8000))

SET @FilePath = '\\ServerName\xmlFiles\xmlFile1.xml'
SET @xCmd = 'type ' + @FilePath
SET @FileContents = ''
INSERT INTO #temp EXEC master.dbo.xp_cmdshell @xCmd
DECLARE @x INT
DECLARE @y INT
SET @x = 0
SELECT @y = count(*) from #temp
END
SELECT @FileContents as FileContents

Output:

read the xml file from sql server

There are different options available to read particular node value of xml file. I will explain them one by one.

Method 1:

DECLARE @MyXML XML
SET @MyXML = @FileContents
SELECT  fields.value('ITEMNMBR[1]', 'varchar(30)') AS ITEMNMBR,
        fields.value('ITEMDESC[1]', 'varchar(30)') AS ITEMDESC,
        fields.value('QTY[1]', 'varchar(30)') AS QTY,
        fields.value('TPRICE[1]', 'varchar(30)') AS TPRICE
FROM    @MyXML.nodes('//Rate') as  xmldata(fields)
WHERE fields.value('ITEMNMBR[1]', 'varchar(30)') = '101'

Output:

read the xml file from sql server

Method 2:

CREATE TABLE #docs (pk INT PRIMARY KEY, xCol XML) INSERT INTO #docs VALUES (1, @FileContents)

SELECT nref.value('ITEMNMBR[1]', 'nvarchar(50)') ITEMNMBR, 
       nref.value('ITEMDESC[1]', 'nvarchar(50)') ITEMDESC, 
       nref.value('QTY[1]', 'nvarchar(50)') QTY,
       nref.value('TPRICE[1]', 'nvarchar(50)') TPRICE       
FROM   #docs CROSS APPLY xCol.nodes('/Price/Rate') AS R(nref)
WHERE  nref.exist('.[ITEMNMBR = "100"]') = 1

Output:

read the xml file from sql server

Method 3:

DECLARE @idoc INT EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

SELECT * FROM OPENXML (@idoc, 'Price/Rate', 2) WITH (ITEMNMBR varchar(50) 'ITEMNMBR', ITEMDESC varchar(50) 'ITEMDESC', QTY varchar(50) 'QTY', TPRICE varchar(50) 'TPRICE' ) R WHERE R.ITEMNMBR = '101'

EXEC sp_xml_removedocument @idoc

Output:

read the xml file from sql server

Join the conversation

What are your thoughts on this blog? Drop us a line below. We’d love to hear from you.

© 2017 Nous Infosystems Pvt. Ltd. All rights reserved.