Stored Procedure which accept xml file as input

Question

<?xml version="1.0"?>
<catalog>
 <book id="bk101">
 <author>Gambardella, Matthew</author>
 <title>XML Developer's Guide</title>
 <genre>Computer</genre>
 <price>44.95</price>
 <publish_date>2000年10月01日</publish_date>
 <description>An in-depth look at creating applications 
 with XML.</description>
 </book>
 <book id="bk102">
 <author>Ralls, Kim</author>
 <title>Midnight Rain</title>
 <genre>Fantasy</genre>
 <price>5.95</price>
 <publish_date>2000年12月16日</publish_date>
 <description>A former architect battles corporate zombies, 
 an evil sorceress, and her own childhood to become queen 
 of the world.</description>
 </book>
</catalog>

I have xml file as above.

In my SQL Server 2008 database there is table Book__Master In which all the columns are same as elements in above xml file i.e. there are columns like bookid, author, title etc.

Now I want to write a stored procedure which accepts this xml file as input parameter and stores the element's values as it is in their respective columns.

Can anyone help me on this that how I can write this type of stored procedure?

• Nathan C: This sounds like a homework assignment...

Answer 1

You can use X-Query for that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE PROCEDURE dbo.ParseXML
 @XML XML
AS
BEGIN
 SELECT 
 Book.value('@id','NVARCHAR(100)') AS id,
 Book.value('author[1]','NVARCHAR(100)') AS author,
 Book.value('title[1]','NVARCHAR(100)') AS title,
 Book.value('price[1]','DECIMAL(10,4)') AS price,
 Book.value('publish_date[1]','DATE') AS publish_date
 FROM @XML.nodes('catalog/book')Catalog(Book)
RETURN;
END;

Query 1:

DECLARE @FileXML XML = '<?xml version="1.0"?>
<catalog>
 <book id="bk101">
 <author>Gambardella, Matthew</author>
 <title>XML Developer''s Guide</title>
 <genre>Computer</genre>
 <price>44.95</price>
 <publish_date>2000年10月01日</publish_date>
 <description>An in-depth look at creating applications 
 with XML.</description>
 </book>
 <book id="bk102">
 <author>Ralls, Kim</author>
 <title>Midnight Rain</title>
 <genre>Fantasy</genre>
 <price>5.95</price>
 <publish_date>2000年12月16日</publish_date>
 <description>A former architect battles corporate zombies, 
 an evil sorceress, and her own childhood to become queen 
 of the world.</description>
 </book>
</catalog>';
EXEC dbo.ParseXML @FileXML;

Results :

| ID | AUTHOR | TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 | 2000年10月01日 |
| bk102 | Ralls, Kim | Midnight Rain | 5.95 | 2000年12月16日 |

This shows you how to get to the values. From there you can easily insert them into your table.