SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


capturing multiple attributes from node


capturing multiple attributes from node

Author
Message
eripey
eripey
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
If I have an xml file below


<xml>
<Document>
<FileDetail>
<BookNumber>12345</BookNumber><BookAuthorLoop><Author name="John Doe"><Author name="Jane Doe"></DetailLoop>
</FileDetail>
</Document>



Right now my sql code looks like this and only captures the first author, how do I get it to get both Authors name, do I need to loop it of some kind?


Insert into tblBook
Select *
From OpenXML
With (
BookNumber varchar(50) '/Document/FileDetail/BookNumber',
BookAuthor varchar(500) '/Document/FileDetail/BookAuthorLoop/Author/@name'
)



The table looks like this
BookNumber  BookAuthor
1234 John Doe



I would like it to capture all authors like this
Book Number         Book Author
1234 John Doe, Jane Doe



Any help/guidance will be greatly appreciated. Thanks.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3192 Visits: 1779
Hi,
I would use the newer XML functions rather then the old OPENXML. Syntax wise you could probably do this in a couple of ways, but here is one that I have come up with (i've tweaked your example xml to make it valid)


DECLARE @xml XML
SET @xml = '<xml>
<Document>
<FileDetail>
<BookNumber>12345</BookNumber>
<BookAuthorLoop>
<Author name="John Doe"/>
<Author name="Jane Doe"/>
</BookAuthorLoop>
</FileDetail>
</Document>
</xml>'

SELECT a.c.value('(BookNumber/text())[1]', 'int')
, b.c.value('(@name)[1]', 'varchar(30)')
FROM @xml.nodes('/xml/Document/FileDetail') AS a(c)
CROSS APPLY a.c.nodes('BookAuthorLoop/Author') AS b(c)


eripey
eripey
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
The new xml functions runs more effeciently than the old OPENXML, that is why you suggest this? If I have a file I need to load, I was use

SET @xml = 'location of xml'



In your example does the brackets [1] specify that there are more than one value or is it the cross apply? This would work whether there would be 1,2 and 3 or more authors, correct? So in your example I would do this:

Insert into tblBook
SELECT a.c.value('(BookNumber/text())[1]', 'int')
, b.c.value('(@name)[1]', 'varchar(30)')
FROM @xml.nodes('/xml/Document/FileDetail') AS a(c)
CROSS APPLY a.c.nodes('BookAuthorLoop/Author') AS b(c)



I would then I have one row of record containing
BookNumber             Book Author
1234 John Doe, Jane Doe



When I query for the book number in the table?

Thanks for taking the time to help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search