none
Stored Procedures using XQuery to insert XML data into tables

    Question

  • If I have the following XML being passed as a parameter into a stored procedure.

     

    <bookstore>
    <name>My Bookstore</name><br/>
    <location>New York</location><br/>
    <book>
     <title lang="eng">Harry Potter</title>
     <price>29.99</price>
    <tableOfContents>
    <topic>
    <title>Harry Potter Topic 1</title>
    <page>2</page>
    </topic>
    <topic>
    <title>Harry Potter Topic 2</title>
    <page>5</page>
    </topic>
    </tableOfContents>
    </book>
    <book>
     <title lang="eng">Learning XML</title>
     <price>39.95</price>
    <tableOfContents>
    <topic>
    <title>Learning XML Topic 1</title>
    <page>1</page>
    </topic>
    <topic>
    <title>Learning XML Topic 2</title>
    <page>2</page>
    </topic>
    </tableOfContents>
    </book>
    </bookstore>
    

    I want to repeatedly execute a stored procedure named "insertBook" passing in each of the 'book' nodes found in the given xml one at a time.

    I also want the SP 'insertBook' to in turn repeatedly execute another storedProcedure 'insertTopics' passing in each 'topic' node found within the given 'book' node one at a time.

    My relational table structure in SQL Server 2005 contains a 'Book' table with auto generated int identity, and a 'Topic' table that has 'BookId' as a foreign key into the identity of the 'Book' table.

    I need to pass in the BookId (int) value along with the 'Topic' node (xml) when invoking SP 'insertTopic' from within 'insertBook'.

    Can somebody please provide something like pseudo code to achieve the above described. I am new to Stored Procedures and SQL XML, XQuery etc.

    Thanks.

     

    Thursday, September 30, 2010 3:54 PM

All replies

  • Hi,

    Could you please elaborate a bit and post the table DDLs? I think you can refer to the following sample to insert values into two tables:

    DECLARE @Books TABLE (BookID int identity(1,1),BookTitle varchar(50),BookLanguage varchar(20),BookPrice decimal(18,2))
    DECLARE @Topics TABLE (TopicID int identity(1,1),BookID int,TopicTitile varchar(50),Page int)
    
    DECLARE @xml XML
    SET @xml = '
    <bookstore>
    	<name>My Bookstore</name><br/>
    	<location>New York</location><br/>
    	<book>
    		<title lang="eng">Harry Potter</title>
    		<price>29.99</price>
    		<tableOfContents>
    			<topic>
    				<title>Harry Potter Topic 1</title>
    				<page>2</page>
    			</topic>
    			<topic>
    				<title>Harry Potter Topic 2</title>
    				<page>5</page>
    			</topic>
    		</tableOfContents>
    	</book>
    	<book>
    		<title lang="eng">Learning XML</title>
    		<price>39.95</price>
    		<tableOfContents>
    			<topic>
    				<title>Learning XML Topic 1</title>
    				<page>1</page>
    			</topic>
    			<topic>
    				<title>Learning XML Topic 2</title>
    				<page>2</page>
    			</topic>
    		</tableOfContents>
    	</book>
    </bookstore>'
    
    INSERT INTO @Books 
    SELECT T.c.value('title[1]','varchar(50)') AS 'BookTitle',
    	T.c.value('(title/@lang)[1]','varchar(20)') AS 'BookLanguage',
    	T.c.value('price[1]','decimal(18,2)') AS 'BookPrice'
    FROM @xml.nodes('/bookstore/book') T(c)
    
    INSERT INTO @Topics
    SELECT b.BookID,n.x.value('title[1]','varchar(50)') AS 'TopicTitile',
    	n.x.value('page[1]','int') AS 'TopicPage'
    FROM @Books b 
    cross apply @xml.nodes('/bookstore/book/tableOfContents/topic[../../title=sql:column("b.BookTitle")]') n(x) 
    
    SELECT BookID,BookTitle,BookLanguage,BookPrice FROM @Books
    SELECT TopicID,BookID,TopicTitile,Page FROM @Topics
    

    References:

    xml Data Type Methods
    http://msdn.microsoft.com/en-us/library/ms190798.aspx

    XQuery Language Reference (Database Engine)
    http://msdn.microsoft.com/en-us/library/ms189075.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 04, 2010 7:26 AM
  • Thank you for replying. Can you please explain this line?

    cross apply @xml.nodes('/bookstore/book/tableOfContents/topic[../../title=sql:column("b.BookTitle")]') n(x)
    

     

    Tuesday, October 05, 2010 2:54 PM
  • It is used to return the respective topic fragments of inserted books. 

    Please see:

    sql:column() Function (XQuery)
    http://msdn.microsoft.com/en-us/library/ms191214.aspx

    nodes() Method (xml Data Type)
    http://msdn.microsoft.com/en-us/library/ms188282.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 07, 2010 6:58 AM