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 XMLSELECT @ExportData ='<Data Number="A123"><BulkData><EachData Parts="Test1" /><EachData Parts="Test2" /><EachData Parts="Test3" /></BulkData></Data>' |
Archive for the ‘XML – SQL Server’ Category
SQL SERVER: Check if Node exists in XML or not
08/12/2009How to Read values from XML Variable/Columns with SQL SERVER 2005
12/23/2008Read 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:
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: