Read xml file content or node value of xml file from sql server

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 @FilePath varchar(255)
DECLARE @xCmd VARCHAR(255)
DECLARE @FileContents VARCHAR(MAX)

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

SET @FilePath = '\\ServerAddress.com\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

WHILE @x <> @y
    BEGIN
        SET @x = @x + 1
        SELECT @FileContents = @FileContents + ThisLine from #temp WHERE PK = @x
    END

SELECT @FileContents as FileContents

There are different options available to read particular node value of xml file. I will explain them one by one. My default xml file will be in below format:

<?xml version="1.0" standalone="yes" ?>
<Price xmlns="http://tempuri.org/Price.xsd">
  <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>

Method 1:

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

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

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