shred data into table by using xml

Question

I'm studying XML/SQL and have the following question. I would like to shred XML data into a table. but my question is: I have many authors using the same tag (<author>) inside the same book category. I would like to select ALL the authors, but I can't achieve this point. Could you please help me to do that?

The result should look like:

category    title              author             author1        author2
=============================================================================
CHILDREN    Harry Potter       J K. Rowling       NULL           NULL
WEB         XQuery Kick Start  James McGovern     Per Bothner    Kurt Cagle

Code:

declare @int int
declare @var xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
 <bookstore>

 <book category="CHILDREN">
   <title lang="en">Harry Potter</title>
   <author>J K. Rowling</author>
   <year>2005</year>
   <price>29.99</price>
 </book>

 <book category="WEB">
   <title lang="en">XQuery Kick Start</title>
   <author>James McGovern</author>
   <author>Per Bothner</author>
   <author>Kurt Cagle</author>
   <year>2003</year>
   <price>49.99</price>
 </book>

 </bookstore>';

EXEC sp_xml_preparedocument @int OUTPUT, @var


SELECT
*
into MyTable
FROM
OPENXML(@int, 'bookstore/book', 11)
with
(
category varchar(100),
title varchar(100),
author varchar(100)
author1 varchar(100),
author2 varchar(100)
);

obs: i'm new at Stackoverflow, so I dont know how to format correctly my result table. I'm feeling sorry for that. — user2868572, Oct 10 '13 at 19:37

marc_s · Answer 1 · 2013-10-10T20:48:42.390

If you know that you'll never have more than 3 authors, you can use something like this:

SELECT
    XBook.value('@category', 'varchar(20)'),
    XBook.value('(title)[1]', 'varchar(50)'),
    XBook.value('(year)[1]', 'int'),
    XBook.value('(price)[1]', 'decimal(10,2)'),
    XBook.value('(author)[1]', 'varchar(50)'),
    XBook.value('(author)[2]', 'varchar(50)'),
    XBook.value('(author)[3]', 'varchar(50)')
FROM 
    @var.nodes('/bookstore/book') AS XTbl(XBook)

I personally find using the native XQuery support much easier than the old, clunky OPENXML approach (which also suffered from memory leaks and other deficiencies).

This gives you an output like this:

enter image description here

score 0 · Answer 2 · answered Oct 10 '13 at 19:43

This is one way...

SELECT
*
into MyTable
FROM
OPENXML(@int, 'bookstore/book', 11)
with
(
category varchar(100) '@category',
title varchar(100) 'title',
author varchar(100) 'author[1]',
author1 varchar(100) 'author[2]',
author2 varchar(100) 'author[3]'
);

And here is another way, with a different structure.

SELECT
*
into MyTable
FROM
OPENXML(@int, 'bookstore/book/author', 11)
with
(
category varchar(100) '../@category',
title varchar(100) '../title',
author varchar(100) '.'
);

Note that when you use the second method, you will get 4 rows with your sample data because the first book has 1 author while the second book has 3 authors. Note that I changed the OPENXML line to start a little further down in the hierarchy.

shred data into table by using xml

2 Answers2

Linked