Retrieving data from xml column using XQuery

Question

Post reply

Retrieving data from xml column using XQuery

snoopysugi

SSC Journeyman

Points: 86
More actions
September 28, 2010 at 6:09 am

#229850

Hi,
I have a table which has an xml column. I want to retrieve the data from the xml column and store the value in a different table.
My xml looks like this
<Root>
<Data>
<Test attr= null/>
</Data>
<Data>
<Test attr = "http://">
<Node1>testname</Node1>
<Node2>retrieve this</Node2>
</Test>
</Data>
<Data>
<Test attr = "http://">
<Node1>testname</Node1>
</Test>
</Data>
</Root>
Now i want to get all value from the "Test" node which has child nodes where "Node2" is not null.
I am able to get the count of the child nodes available in the "Root". But how do i iterate and get the values from each node?
Any help is appreciated.
Thanks
Lisalore

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

How's this?

DECLARE @test TABLE (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

MyXML XML);

INSERT INTO @test

VALUES ('<Root>

<Data>

<Test attr = "http://">

<Node1>testname</Node1>

<Node2>retrieve this</Node2>

</Test>

</Data>

<Data>

<Test attr = "http://">

<Node1>testname</Node1>

</Test>

</Data>

<Data>

<Test attr = "http://">

<Node1>testname2</Node1>

<Node2>also retrieve this</Node2>

</Test>

</Data>

</Root>');

/*

-- NOTE: I can't get it to insert into the table with this portion of the XML.

-- not sure why, but I'm also not an XML expert.

<Data>

<Test attr= null/>

</Data>

*/

SELECT r.data.value('.', 'varchar(50)')

FROM @test t

CROSS APPLY t.MyXML.nodes('/Root/Data/Test/Node2') AS r(data)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

snoopysugi SSC Journeyman Points: 86 More actions · Answer 2

Hi WayneS,

Thanks a lot for your reply.Your solution works great.

Thanks,

Lisalore