How to Read xml file from sql server?                                  

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:

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:

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:

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: