Hi All,

I have an XML that has a layout as follows:
<books>
<book id='12' name='test1'>
<authors>
<author id='1' name = 'jill' />
<author id='2' name = 'jack' />
<author id='3' />
</authors>
</book>
</books>

I want to use XQuery to get data as follows:
book_id author_id author_name
12 1 jill
12 2 jack
12 3 null

How can I accomplish this?
Thanks in advance,
Ben



*** Sent via Developersdex http://www.developersdex.com ***

Ben wrote:
DECLARE @x xml;
SET @x = N'<books>
<book id="12" name="test1">
<authors>
<author id="1" name = "jill" />
<author id="2" name = "jack" />
<author id="3" />
</authors>
</book>
</books>';

SELECT
T.a.value('../../@id', 'int') as book_id,
T.a.value('@id', 'int') as author_id,
T.a.value('@name', 'nvarchar(20)') as author_name
FROM @x.nodes('books/book/authors/author') T(a);


--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

That works !! Thanks much
Ben

*** Sent via Developersdex http://www.developersdex.com ***