InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:36:08 GMT20http://www.sqlservercentral.com/Forums/Topic1390918-21-1.aspxThe 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[code="sql"]SET @xml = 'location of xml'[/code]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:[code="sql"]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)[/code]I would then I have one row of record containing[code="other"]BookNumber Book Author1234 John Doe, Jane Doe[/code]When I query for the book number in the table?Thanks for taking the time to help.Fri, 30 Nov 2012 10:00:08 GMTeripeyhttp://www.sqlservercentral.com/Forums/Topic1390918-21-1.aspxHi,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)[code="sql"]DECLARE @xml XMLSET @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)[/code]Fri, 30 Nov 2012 08:07:08 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1390918-21-1.aspxIf I have an xml file below[code="xml"]<xml><Document><FileDetail><BookNumber>12345</BookNumber><BookAuthorLoop><Author name="John Doe"><Author name="Jane Doe"></DetailLoop></FileDetail></Document>[/code]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?[code="sql"]Insert into tblBookSelect *From OpenXMLWith (BookNumber varchar(50) '/Document/FileDetail/BookNumber',BookAuthor varchar(500) '/Document/FileDetail/BookAuthorLoop/Author/@name')[/code]The table looks like this[code="other"]BookNumber BookAuthor1234 John Doe[/code]I would like it to capture all authors like this[code="other"]Book Number Book Author1234 John Doe, Jane Doe[/code]Any help/guidance will be greatly appreciated. Thanks.Thu, 29 Nov 2012 17:17:41 GMTeripey