Sql-server – Stored Procedure which accept xml file as input

sql-server-2008-r2stored-proceduresxml

<?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?

Best Answer

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.

Related Solutions

Sql-server – stored proc to handle XML as input param for multiple record inserts

I c an't speak for performance but this is one way of doing things.

declare @x xml

select @x = '<MessageRecipient> 
<MessageId>52</MessageId> 

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient> 

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient> 
</MessageRecipient>'


select 
    Finaldata.R.value ('../MessageId[1]', 'NVARCHAR(10)') AS MessageID
    , Finaldata.R.value ('RecipientTypeId[1]', 'INT') AS RecipientTypeId
    , Finaldata.R.value ('EmailAddress[1]', 'NVARCHAR(200)') AS EmailAddress
    , Finaldata.R.value ('FriendlyName[1]', 'NVARCHAR(100)') AS FriendlyName
    , Finaldata.R.value ('UFID[1]', 'NVARCHAR(10)') AS UFID
from @x.nodes ('//MessageRecipient/Recipient') as Finaldata (R)

The best way to insert a XML input to an oracle table in stored procedure

You can query your XML data directly in SQL with XMLTABLE:

SQL> VARIABLE xml VARCHAR2(4000);
SQL> BEGIN :xml :=
  2  '<rowset>
  3       <row>
  4           <name>tom</name>
  5           <family>mack</family>
  6       </row>
  7       <row>
  8           <name>marry</name>
  9           <family>skot</family>
 10       </row>
 11   </rowset>';
 12  END;
 13  /

PL/SQL procedure successfully completed

SQL> SELECT x.*
  2    FROM XMLTable('for $i in /rowset/row return $i'
  3                    passing XMLTYPE(:xml) columns
  4                       name VARCHAR2(200) path 'name',
  5                       family VARCHAR2(200) path 'family'
  6                  ) x;

NAME    FAMILY
------- -------
tom     mack
marry   skot

You can also use XMLSequence and EXTRACT (see this SO question for example) but as noted in the documentation these functions are deprectated.

Another option is DBMS_XMLSTORE, although you can't really customize it much (needs perfect match between input xml tags and output column names):

SQL> CREATE TABLE my_table ("name" VARCHAR2(10), "family" VARCHAR2(10));

Table created

SQL> DECLARE
  2     l_ctx  dbms_xmlstore.ctxtype;
  3     l_rows NUMBER;
  4  BEGIN
  5     l_ctx := dbms_xmlstore.newcontext('MY_TABLE'); -- Get saved context
  6     dbms_xmlstore.clearUpdateColumnList(l_ctx); -- Clear the update settings
  7  
  8     -- Set the columns to be updated/inserted as a list of values
  9     dbms_xmlstore.setUpdateColumn(l_ctx, 'name');
 10     dbms_xmlstore.setUpdateColumn(l_ctx, 'family');
 11     -- Set ROW tag
 12     dbms_xmlstore.setRowTag(l_ctx, 'row');
 13  
 14    -- Insert the doc.
 15    l_rows := DBMS_XMLSTORE.insertXML(l_ctx, :xml);
 16    DBMS_OUTPUT.put_line(l_rows || ' rows inserted.');
 17  
 18    -- Close the context
 19    DBMS_XMLSTORE.closeContext(l_ctx);
 20  
 21  END;
 22  /

2 rows inserted.

PL/SQL procedure successfully completed

SQL> select * from my_table;

name       family
---------- ----------
tom        mack
marry      skot

I'm not sure performance-wise which option is faster, this certainly depends on the data -- you'll have to benchmark.

Best Answer

Related Solutions

Sql-server – stored proc to handle XML as input param for multiple record inserts

The best way to insert a XML input to an oracle table in stored procedure

Related Question