November 29, 2012 at 5:17 pm
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.
November 30, 2012 at 8:07 am
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)
November 30, 2012 at 10:00 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply