CODEWITHSUNDEEP
sqlsql-serverxml
user2868572
user2868572

Reputation: 1

shred data into table by using xml

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)
);

Upvotes: 0

Views: 75

Answers (2)

marc_s
marc_s

Reputation: 754468

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

Upvotes: 2

George Mastros
George Mastros

Reputation: 24498

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.

Upvotes: 0

Related Questions