Posts Tagged ‘SQL SERVER WITH XML’

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

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