how to save multiple rows in SQL using XML?

Question

I have following XML, and i wish to save its data in my SQL table. I have a table named as tblDummy it has three columns "JobID" "ItemID" "SubitemID". there can be multiple subitemsid for particular combination of Jobid and Itemid. How can i do this?

<jobs>
   <job>
     <jobid>4711</jobid>
     <items>
     <itemid>1</itemid>
       <subitems>
        <subitemid>1</subitemid>
        <subitemid>2</subitemid>
       </subitems>
    <itemid>2</itemid>
       <subitems>
        <subitemid>7</subitemid>
        <subitemid>10</subitemid>
       </subitems>
    <itemid>9</itemid>
       <subitems>
        <subitemid>12</subitemid>
        <subitemid>16</subitemid>
       </subitems>
    </items>
   </job>  
 </jobs>

Answer 1

As this XML is, you cannot properly parse it. You would need to change it - you should put each item with its itemid and subitems into a separate <item> node - otherwise you just have a long list of <itemid> and <subitems> nodes under your <items> main node, but you have no means of telling which <itemid> and <subitems> nodes belong together ....

You need to change your XML to be something like this:

<job>
   <jobid>4711</jobid>
   <items>
      <item>
         <itemid>1</itemid>
         <subitems>
             <subitemid>1</subitemid>
             <subitemid>2</subitemid>
         </subitems>
      </item>   
      <item>
         <itemid>2</itemid>
          <subitems>
            ......
          </subitems>
      </item>   
      ... (possibly more <item> nodes) ....
   </items>
</job>

THEN you could use basically the same code I had for your previous question - extended to cover three levels:

CREATE PROCEDURE dbo.SaveJobs (@input XML)
AS BEGIN

;WITH JobsData AS
(    
    SELECT
        JobID = JobNode.value('(jobid)[1]', 'int'),
        ItemID = ItemNode.value('(itemid)[1]', 'int'),
        SubItemID = SubItemNode.value('.', 'int')
    FROM 
        @input.nodes('/jobs/job') AS TblJobs(JobNode)
    CROSS APPLY
        JobNode.nodes('items/item') AS TblItems(ItemNode)
    CROSS APPLY
        ItemNode.nodes('subitems/subitem') AS TblSubItems(SubItemNode)
)
INSERT INTO dbo.tblDummy(JobID, ItemID, SubItemID)
   SELECT JobID, ItemID, SubItemID
   FROM JobsData
END

Basically, you need three "lists" of XML nodes:

• first you need the list of all <jobs>/<job> nodes to get the jobid values
• for each of those job nodes, you will also need to get their list of nested <items>/<item> to get the itemid value
• from each node, you then also get the list of <subitems>/<subitem>

This will most likely work - but most likely, it will be rather slow (three nested calls to the .nodes() function!).

Update:

OK, so the first call @input.nodes('/jobs/job') AS TblJobs(JobNode) basically creates a "pseudo" table TblJobs with a single column JobNode and each <job> element in your XML is being stored into a row in that pseudo table - so the first row will contain this XML in it's JobNode column:

<job>
   <jobid>4711</jobid>
   <items>
      <item>
         <itemid>1</itemid>
         <subitems>
             <subitemid>1</subitemid>
             <subitemid>2</subitemid>
         </subitems>
      </item>   
      <item>
         <itemid>2</itemid>
          <subitems>
            ......
          </subitems>
      </item>   
      ... (possibly more <item> nodes) ....
   </items>
</job>

and each further row will contain the additional XML fragments for each subsequent <job> element inside <jobs>

From each of those XML fragments, the second call

CROSS APPLY JobNode.nodes('items/item') AS TblItems(ItemNode)

again selects a list of XML fragments into a pseudo table (TblItems) with a single column ItemNode that contains the XML fragment for each <item> node inside that <job> node we're dealing with currently.

So the first row in this pseudo-table contains:

     <item>
         <itemid>1</itemid>
         <subitems>
             <subitemid>1</subitemid>
             <subitemid>2</subitemid>
         </subitems>
      </item>   

and the second row will contain

      <item>
         <itemid>2</itemid>
          <subitems>
            ......
          </subitems>
      </item>   

and so on.

And then the third call - you've guessed it - again extracts a list of XML elements as rows into a pseudo-table - one entry for each <subitem> node in your XML fragment.

Update #2:

I'm new to "JobID = JobNode.value('(jobid)[1]', 'int')" line of code

OK - given the <Job> XML fragment that you have:

<job>
   <jobid>4711</jobid>
   <items>
     ......
   </items>
</job>

the .value() call just executes this XPath expression (jobid) on that XML and basically gets back the <jobid>4711</jobid> snippet. It then extracts the value of that node (the inner text), and the second parameter of the .value() call defines what SQL data type to interpret this as - so it basically grabs the 4711 from the <jobid> node and interprets it as an int

Answer 2

You can take a composite key of Jobid and Itemid as primary key.