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

SSRS - Clear multi-value cascading dropdown selected value

Most of us are facing problems that multi-value cascading dropdown selected value is not clearing when changing the value in parent dropdown.

For example I am having two dropdowns State and City in SSRS report. Based on State dropdown selected value, City dropdown values will be populated.

In my sample, State dropdown will have two values "Tamilnadu" and "Karnataka".

Cities for Tamilnadu will be "Trichy" and "C.City".

City for Karnataka will be "C.City".

Initially in State dropdown I will select Tamilnadu and in City dropdown I will select C.City.

Then if I select Karnataka in state dropdown city dropdown will be populated with C.City. But the problem is by default it will be selected as shown below.

But we may need that when it is populating newly, no values should be selected by default.


The solution is very simple.

In Report Parameters, city parameter has value field and label field as City. This is the root cause for this problem.

If I change the value field to state like below, the problem will be solved.

Because the state value is always changing. So the report will assume it as a different item. We can get the desired output.