如何使用XML在SQL中保存多行？

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?

我有以下XML，我希望将其数据保存在我的SQL表中。我有一个名为tblDummy的表，它有三列“JobID”“ItemID”“SubitemID”。对于Jobid和Itemid的特定组合，可以有多个子项。我怎样才能做到这一点？

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

2 个解决方案

#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 ....

就像这个XML一样，你无法正确地解析它。您需要更改它 - 您应该将每个项目及其itemid和子项目放入一个单独的节点 - 否则您只需在主节点下有一个很长的和节点列表，但是你无法告诉哪个和节点属于一起....

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

您需要将XML更改为以下内容：

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

基本上，您需要三个XML节点“列表”：

first you need the list of all <jobs>/<job> nodes to get the jobid values
首先，您需要所有 / 节点的列表来获取jobid值
for each of those job nodes, you will also need to get their list of nested <items>/<item> to get the itemid value
对于每个作业节点，您还需要获取嵌套 / 的列表以获取itemid值
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!).

这很可能会起作用 - 但很可能，它会相当慢（对.nodes（）函数的三次嵌套调用！）。

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:

好的，所以第一次调用@input.nodes（'/ jobs / job'）AS TblJobs（JobNode）基本上创建了一个带有单列JobNode的“伪”表TblJobs，并且XML中的每个元素都存储在一个该伪表中的行 - 所以第一行将在其JobNode列中包含此XML：

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

并且每个其他行将包含中每个后续元素的附加XML片段

From each of those XML fragments, the second call

从每个XML片段，第二个调用

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.

再次选择一个XML片段列表到一个伪表（TblItems）中，该列包含一个ItemNode列，该列包含我们当前正在处理的节点内每个节点的XML片段。

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.

然后第三个调用 - 您已经猜到了 - 再次将XML元素列表作为行提取到伪表中 - XML片段中每个节点的一个条目。

Update #2:

更新＃2：

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

我是新手“JobID = JobNode.value（'（jobid）[1]'，'int'）”代码行

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

好的 - 给定您拥有的 XML片段：

<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

.value（）调用只是对该XML执行此XPath表达式（jobid），并基本上返回 4711 代码段。然后它提取该节点的值（内部文本），并且.value（）调用的第二个参数定义要将其解释为的SQL数据类型 - 因此它基本上从节点获取4711并解释它作为一个int

#2

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

您可以将Jobid和Itemid的组合键作为主键。

#1