capturing multiple attributes from node

• eripey

  SSC Rookie

  Points: 45

  More actions

  November 29, 2012 at 5:17 pm

  #98575

  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

  SSCertifiable

  Points: 6757

  More actions

  November 30, 2012 at 8:07 am

  #1564386

  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

  SSC Rookie

  Points: 45

  More actions

  November 30, 2012 at 10:00 am

  #1564476

  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.