Archive for the ‘XML’ Category

h1

SQL SERVER: Check if Node exists in XML or not

08/12/2009

Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
  <BulkData>
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
  </BulkData>
</Data>'

Now I need to check if “BulkData” node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')

This will return “1” if node is exists else return “0”.

That’s it. I can write based on the return result by this statement.

Let me know if it helps you.

Reference : Tejas Shah (http://www.SQLYoga.com)

h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List

Get Comma separated List

I want output like:

Get Comma separated List

Get Comma separated List

I come up with very good solution. Let me share with all of you:

Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

h1

SQL SERVER How to Get Stored Procedure Names used by Reports, SQL Reporting Services

02/21/2009

We are using SQL Reporting Services for Reports.

Today we need to identify Stored Procedures used by Reports, so we want to transfer those SPs to Replication server.
I found very good query on Jacob Sebastian Blog.

;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
    NAME AS reportname,
    q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
    x.value('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
    x.value('CommandText[1]', 'VARCHAR(50)') AS spname
FROM
(
    SELECT NAME,
    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
    FROM reportserver.dbo.catalog
) a
CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
CROSS apply q.nodes('Query') r(x)
h1

How to Read values from XML Variable/Columns with SQL SERVER 2005

12/23/2008

Read Attributes value from XML variable:

DECLARE @xml XML
SELECT @xml = 
'<TejasShah>
  <Att ID="Tejas" Text="TejasShah" />
  <Att ID="Tejas2" Text="TejasShah2" />
  <Att ID="Tejas3" Text="TejasShah3" />
</TejasShah>'

SELECT  x.v.value('@ID[1]', 'VARCHAR(100)') AS [ID],
        x.v.value('@Text[1]', 'VARCHAR(100)') AS [Text]
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be:

Read values from xml variable

In other way, we can  also get value from ELEMENTS of xml variable. Lets see:

DECLARE @xml XML
SELECT @xml = '
<TejasShah>
  <Att>
    <ID>Tejas</ID>
    <Text>TejasShah</Text>
  </Att>
  <Att>
    <ID>Tejas2</ID>
    <Text>TejasShah2</Text>
  </Att>
  <Att>
    <ID>Tejas3</ID>
    <Text>TejasShah3</Text>
  </Att>
</TejasShah>'
SELECT  x.v.value('ID[1]', 'VARCHAR(100)'),
        x.v.value('Text[1]', 'VARCHAR(100)')
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be the same as above:

Read values from XML Elements