Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

capturing multiple attributes from node Expand / Collapse
Author
Message
Posted Thursday, November 29, 2012 5:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 05, 2013 12:03 PM
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.
Post #1390918
Posted Friday, November 30, 2012 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 2,423, Visits: 1,566
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)

Post #1391338
Posted Friday, November 30, 2012 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 05, 2013 12:03 PM
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.
Post #1391434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse