XML数据仅将一行导入SQL TAble中。

Question

我将XML数据导入到SQL表中。我希望将数据从XML提取到SQL中的一个表中。数据是包含1到多个行项的采购订单的数据。目前只有1行项目的细节是开始提取。我猜想它与“text()1”部分有关。有人能帮我更正一下我的剧本吗？我目前的代码是：

DECLARE @xml XML
SELECT @xml = (SELECT xmldata FROM INTELLECTION.dbo.XMLwithOpenXML_EGEN_COUPA_PO  where [PO_NUMBER] =@filename2) ;

insert into INTELLECTION.dbo.COUPA_PO_Data([PO_Number],[PO_LineNumber],[Description],[Total_Amount],[UnitPrice],[Quantity],[Order_type],[Currency],[UOM],Suppliername,SupplierNumber )
SELECT
t.c.value('(./order-lines/order-line/order-header-number/text())[1]', 'VARCHAR(100)') as PO_Number,
t.c.value('(./order-lines/order-line/line-num/text())[1]', 'VARCHAR(100)') as   PO_LineNumber,
t.c.value('(./order-lines/order-line/description/text())[1]', 'VARCHAR(100)') as   Description,
t.c.value('(./order-lines/order-line/accounting-total/text())[1]', 'decimal(18,2)') as  Total_Amount,
t.c.value('(./order-lines/order-line/price/text())[1]', 'decimal(18,2)') as  UnitPrice,
case when (t.c.value('(./order-lines/order-line/quantity/text())[1]', 'decimal(18,2)')) is null then '1' else t.c.value('(./order-lines/order-line/quantity/text())[1]', 'decimal(18,2)') end as  Quantity,
t.c.value('(./order-lines/order-line/type/text())[1]', 'VARCHAR(100)') as   Order_type,
t.c.value('(./order-lines/order-line/currency/code/text())[1]', 'VARCHAR(100)') as   Currency,
case when t.c.value('(./order-lines/order-line/uom/code/text())[1]', 'VARCHAR(100)') is null then '' else t.c.value('(./order-lines/order-line/uom/code/text())[1]', 'VARCHAR(100)') end as   UOM,
t.c.value('(./order-lines/order-line/supplier/name/text())[1]', 'VARCHAR(100)') as   Suppliername,
t.c.value('(./order-lines/order-line/supplier/number/text())[1]', 'VARCHAR(100)') as   SupplierNumber

FROM @xml.nodes('order-headers/order-header') t(c);

示例XML如下所示：

<?xml version="1.0" encoding="UTF-8"?>
<order-headers type="array">
   <order-header>
      <acknowledged-flag type="boolean">false</acknowledged-flag>
      <created-at type="datetime">2010-12-21T12:08:43-08:00</created-at>
      <id type="integer">2400</id>
      <status>issued</status>
      <transmission-status>sent_via_email</transmission-status>
      <updated-at type="datetime">2010-12-21T14:16:03-08:00</updated-at>
      <version type="integer">1</version>
      <exported type="boolean">false</exported>
      <attachment type="integer" />
      <received type="boolean" />
      <custom-field-2 type="string" />
      <buyer type="string" />
      <custom-field-4 type="string" />
      <date type="datetime" />
      <project2 type="string">DBA1</project2>
      <created-by>
         <email>upgrade90@coupa.com</email>
         <employee-number />
         <firstname>Coupa</firstname>
         <id type="integer">1</id>
         <lastname>Support</lastname>
         <login>coupasupport</login>
      </created-by>
      <requisition-header>
         <id type="integer">1902</id>
         <requester>
            <email>upgrade90@coupa.com</email>
            <employee-number />
            <firstname>Coupa</firstname>
            <id type="integer">1</id>
            <lastname>Support</lastname>
            <login>coupasupport</login>
         </requester>
      </requisition-header>
      <ship-to-address>
         <attention />
         <city>Redwood City</city>
         <id type="integer">1</id>
         <name nil="true" />
         <postal-code>94029</postal-code>
         <state>CA</state>
         <street1>250 Sycamore Avenue</street1>
         <street2 />
         <country>
            <code>US</code>
            <id type="integer">223</id>
            <name>United States</name>
         </country>
      </ship-to-address>
      <ship-to-user>
         <email>upgrade90@coupa.com</email>
         <employee-number />
         <firstname>Coupa</firstname>
         <id type="integer">1</id>
         <lastname>Support</lastname>
         <login>coupasupport</login>
      </ship-to-user>
      <supplier>
         <id type="integer">2</id>
         <name>ABS Services 1</name>
         <number>28</number>
         <primary-contact>
            <email>ben.mlynash@coupa.com</email>
            <id type="integer">327</id>
            <name-additional nil="true" />
            <name-family>Rodriguez</name-family>
            <name-fullname nil="true" />
            <name-given>Paul</name-given>
            <name-prefix nil="true" />
            <name-suffix nil="true" />
            <notes nil="true" />
            <phone-fax>
               <area-code>232</area-code>
               <country-code>1</country-code>
               <extension nil="true" />
               <number>2321192</number>
            </phone-fax>
         </primary-contact>
         <primary-address>
            <attention nil="true" />
            <city>Palo Alto</city>
            <id type="integer">385</id>
            <name>ABS Services 1</name>
            <postal-code>94301</postal-code>
            <state>CA</state>
            <street1>500 Main St</street1>
            <street2 />
            <country>
               <code>US</code>
               <id type="integer">223</id>
               <name>United States</name>
            </country>
         </primary-address>
      </supplier>
      <updated-by>
         <email>upgrade90@coupa.com</email>
         <employee-number />
         <firstname>Coupa</firstname>
         <id type="integer">1</id>
         <lastname>Support</lastname>
         <login>coupasupport</login>
      </updated-by>
      <payment-term>
         <code>Net 45</code>
         <days-for-discount-payment type="integer" nil="true" />
         <days-for-net-payment type="integer" nil="true" />
         <discount-rate type="float" nil="true" />
         <id type="integer">2</id>
      </payment-term>
      <shipping-term>
         <code>UPS -Ground</code>
         <id type="integer">1</id>
      </shipping-term>
      <attachments />
      <order-lines>
         <order-line>
            <accounting-total type="decimal">500.00</accounting-total>
            <created-at type="datetime">2010-12-21T12:08:43-08:00</created-at>
            <description>legal services for Q4</description>
            <id type="integer">1325</id>
            <invoiced type="float">0.00</invoiced>
            <line-num type="integer">1</line-num>
            <need-by-date type="datetime">2010-12-24T00:00:00-08:00</need-by-date>
            <order-header-id type="integer">2400</order-header-id>
            <price type="decimal">500.00</price>
            <quantity type="float" />
            <received type="float">0.00</received>
            <source-part-num />
            <status>received</status>
            <sub-line-num type="integer" />
            <supp-aux-part-num />
            <total type="decimal">500.00</total>
            <type>OrderAmountLine</type>
            <updated-at type="datetime">2010-12-30T13:03:56-08:00</updated-at>
            <version type="integer" />
            <options type="string" />
            <family type="string" />
            <family1 type="string" />
            <test-date type="datetime" />
            <tax-id type="string" />
            <custom-field-2 type="boolean" />
            <custom-field-1 type="boolean" />
            <buyer type="string" />
            <dept type="string" />
            <account>
               <active type="boolean">true</active>
               <code>01-100-8000</code>
               <id type="integer">26</id>
               <name>USA -Marketing, Assets</name>
               <segment-1>01</segment-1>
               <segment-10 nil="true" />
               <segment-11 nil="true" />
               <segment-12 nil="true" />
               <segment-13 nil="true" />
               <segment-14 nil="true" />
               <segment-15 nil="true" />
               <segment-16 nil="true" />
               <segment-17 nil="true" />
               <segment-18 nil="true" />
               <segment-19 nil="true" />
               <segment-2>100</segment-2>
               <segment-20 nil="true" />
               <segment-3>8000</segment-3>
               <segment-4 nil="true" />
               <segment-5 nil="true" />
               <segment-6 nil="true" />
               <segment-7 nil="true" />
               <segment-8 nil="true" />
               <segment-9 nil="true" />
               <account-type>
                  <id type="integer">2</id>
                  <name>Chart of Accounts</name>
               </account-type>
            </account>
            <accounting-total-currency>
               <code>USD</code>
               <id type="integer">1</id>
            </accounting-total-currency>
            <currency>
               <code>USD</code>
               <id type="integer">1</id>
            </currency>
            <commodity>
               <active type="boolean">true</active>
               <created-at type="datetime">2007-11-26T16:03:18Z</created-at>
               <id type="integer">2</id>
               <name>Office Supplies</name>
               <updated-at type="datetime">2009-10-08T23:45:03Z</updated-at>
               <created-by>
                  <email>bmlynash@gmail.com</email>
                  <employee-number>12</employee-number>
                  <firstname>Bob</firstname>
                  <id type="integer">33</id>
                  <lastname>Admin</lastname>
                  <login>admin</login>
               </created-by>
               <updated-by>
                  <email>bmlynash@gmail.com</email>
                  <employee-number>12</employee-number>
                  <firstname>Bob</firstname>
                  <id type="integer">33</id>
                  <lastname>Admin</lastname>
                  <login>admin</login>
               </updated-by>
               <category type="string" />
            </commodity>
            <created-by>
               <email>upgrade90@coupa.com</email>
               <employee-number />
               <firstname>Coupa</firstname>
               <id type="integer">1</id>
               <lastname>Support</lastname>
               <login>coupasupport</login>
            </created-by>
            <supplier>
               <id type="integer">2</id>
               <name>ABS Services 1</name>
               <number>28</number>
               <primary-contact>
                  <email>ben.mlynash@coupa.com</email>
                  <id type="integer">327</id>
                  <name-additional nil="true" />
                  <name-family>Rodriguez</name-family>
                  <name-fullname nil="true" />
                  <name-given>Paul</name-given>
                  <name-prefix nil="true" />
                  <name-suffix nil="true" />
                  <notes nil="true" />
                  <phone-fax>
                     <area-code>232</area-code>
                     <country-code>1</country-code>
                     <extension nil="true" />
                     <number>2321192</number>
                  </phone-fax>
               </primary-contact>
               <primary-address>
                  <attention nil="true" />
                  <city>Palo Alto</city>
                  <id type="integer">385</id>
                  <name>ABS Services 1</name>
                  <postal-code>94301</postal-code>
                  <state>CA</state>
                  <street1>500 Main St</street1>
                  <street2 />
                  <country>
                     <code>US</code>
                     <id type="integer">223</id>
                     <name>United States</name>
                  </country>
               </primary-address>
            </supplier>
            <updated-by>
               <email>upgrade90@coupa.com</email>
               <employee-number />
               <firstname>Coupa</firstname>
               <id type="integer">1</id>
               <lastname>Support</lastname>
               <login>coupasupport</login>
            </updated-by>
            <asset-tags />
            <attachments />
         </order-line>
         <order-line>
            <accounting-total type="decimal">500.00</accounting-total>
            <created-at type="datetime">2010-12-21T12:08:43-08:00</created-at>
            <description>legal services for Q4</description>
            <id type="integer">1325</id>
            <invoiced type="float">0.00</invoiced>
            <line-num type="integer">2</line-num>
            <need-by-date type="datetime">2010-12-24T00:00:00-08:00</need-by-date>
            <order-header-id type="integer">2400</order-header-id>
            <price type="decimal">1500.00</price>
            <quantity type="float" />
            <received type="float">0.00</received>
            <source-part-num />
            <status>received</status>
            <sub-line-num type="integer" />
            <supp-aux-part-num />
            <total type="decimal">1500.00</total>
            <type>OrderAmountLine</type>
            <updated-at type="datetime">2010-12-30T13:03:56-08:00</updated-at>
            <version type="integer" />
            <options type="string" />
            <family type="string" />
            <family1 type="string" />
            <test-date type="datetime" />
            <tax-id type="string" />
            <custom-field-2 type="boolean" />
            <custom-field-1 type="boolean" />
            <buyer type="string" />
            <dept type="string" />
            <account>
               <active type="boolean">true</active>
               <code>01-100-8000</code>
               <id type="integer">26</id>
               <name>USA -Marketing, Assets</name>
               <segment-1>01</segment-1>
               <segment-10 nil="true" />
               <segment-11 nil="true" />
               <segment-12 nil="true" />
               <segment-13 nil="true" />
               <segment-14 nil="true" />
               <segment-15 nil="true" />
               <segment-16 nil="true" />
               <segment-17 nil="true" />
               <segment-18 nil="true" />
               <segment-19 nil="true" />
               <segment-2>100</segment-2>
               <segment-20 nil="true" />
               <segment-3>8000</segment-3>
               <segment-4 nil="true" />
               <segment-5 nil="true" />
               <segment-6 nil="true" />
               <segment-7 nil="true" />
               <segment-8 nil="true" />
               <segment-9 nil="true" />
               <account-type>
                  <id type="integer">2</id>
                  <name>Chart of Accounts</name>
               </account-type>
            </account>
            <accounting-total-currency>
               <code>USD</code>
               <id type="integer">1</id>
            </accounting-total-currency>
            <currency>
               <code>USD</code>
               <id type="integer">1</id>
            </currency>
            <commodity>
               <active type="boolean">true</active>
               <created-at type="datetime">2007-11-26T16:03:18Z</created-at>
               <id type="integer">2</id>
               <name>Office Supplies</name>
               <updated-at type="datetime">2009-10-08T23:45:03Z</updated-at>
               <created-by>
                  <email>bmlynash@gmail.com</email>
                  <employee-number>12</employee-number>
                  <firstname>Bob</firstname>
                  <id type="integer">33</id>
                  <lastname>Admin</lastname>
                  <login>admin</login>
               </created-by>
               <updated-by>
                  <email>bmlynash@gmail.com</email>
                  <employee-number>12</employee-number>
                  <firstname>Bob</firstname>
                  <id type="integer">33</id>
                  <lastname>Admin</lastname>
                  <login>admin</login>
               </updated-by>
               <category type="string" />
            </commodity>
            <created-by>
               <email>upgrade90@coupa.com</email>
               <employee-number />
               <firstname>Coupa</firstname>
               <id type="integer">1</id>
               <lastname>Support</lastname>
               <login>coupasupport</login>
            </created-by>
            <supplier>
               <id type="integer">2</id>
               <name>ABS Services 2222</name>
               <number>22228</number>
               <primary-contact>
                  <email>ben.mlynash@coupa.com</email>
                  <id type="integer">327</id>
                  <name-additional nil="true" />
                  <name-family>Rodriguez</name-family>
                  <name-fullname nil="true" />
                  <name-given>Paul</name-given>
                  <name-prefix nil="true" />
                  <name-suffix nil="true" />
                  <notes nil="true" />
                  <phone-fax>
                     <area-code>232</area-code>
                     <country-code>1</country-code>
                     <extension nil="true" />
                     <number>2321192</number>
                  </phone-fax>
               </primary-contact>
               <primary-address>
                  <attention nil="true" />
                  <city>Palo Alto</city>
                  <id type="integer">385</id>
                  <name>ABS Services 1</name>
                  <postal-code>94301</postal-code>
                  <state>CA</state>
                  <street1>500 Main St</street1>
                  <street2 />
                  <country>
                     <code>US</code>
                     <id type="integer">223</id>
                     <name>United States</name>
                  </country>
               </primary-address>
            </supplier>
            <updated-by>
               <email>upgrade90@coupa.com</email>
               <employee-number />
               <firstname>Coupa</firstname>
               <id type="integer">1</id>
               <lastname>Support</lastname>
               <login>coupasupport</login>
            </updated-by>
            <asset-tags />
            <attachments />
         </order-line>
      </order-lines>
   </order-header>
</order-headers>

Answer 1

您需要将order-lines/order-line移动到.nodes参数中。

SELECT
t.c.value('(order-header-id/text())[1]', 'VARCHAR(100)') as PO_Number,
t.c.value('(line-num/text())[1]', 'VARCHAR(100)') as   PO_LineNumber,
t.c.value('(description/text())[1]', 'VARCHAR(100)') as   Description,
t.c.value('(accounting-total/text())[1]', 'decimal(18,2)') as  Total_Amount,
t.c.value('(price/text())[1]', 'decimal(18,2)') as  UnitPrice,
isnull(t.c.value('(quantity/text())[1]', 'decimal(18,2)'), 1) as  Quantity,
t.c.value('(type/text())[1]', 'VARCHAR(100)') as   Order_type,
t.c.value('(currency/code/text())[1]', 'CHAR(3)') as   Currency,
isnull(t.c.value('(uom/code/text())[1]', 'VARCHAR(100)'), '') as   UOM,
t.c.value('(supplier/name/text())[1]', 'VARCHAR(100)') as   Suppliername,
t.c.value('(supplier/number/text())[1]', 'VARCHAR(100)') as   SupplierNumber

FROM @xml.nodes('order-headers/order-header/order-lines/order-line') t(c);

只有当您想从它获得数据时，您才需要order-header本身，在这种情况下，您可以使用.nodes两次，第二次引用第一次。

例如：

SELECT
h.c.value('(id/text())[1]', 'VARCHAR(100)') as PO_Number,
t.c.value('(line-num/text())[1]', 'VARCHAR(100)') as   PO_LineNumber,
t.c.value('(description/text())[1]', 'VARCHAR(100)') as   Description,
t.c.value('(accounting-total/text())[1]', 'decimal(18,2)') as  Total_Amount,
t.c.value('(price/text())[1]', 'decimal(18,2)') as  UnitPrice,
isnull(t.c.value('(quantity/text())[1]', 'decimal(18,2)'), 1) as  Quantity,
t.c.value('(type/text())[1]', 'VARCHAR(100)') as   Order_type,
t.c.value('(currency/code/text())[1]', 'CHAR(3)') as   Currency,
isnull(t.c.value('(uom/code/text())[1]', 'VARCHAR(100)'), '') as   UOM,
t.c.value('(supplier/name/text())[1]', 'VARCHAR(100)') as   Suppliername,
t.c.value('(supplier/number/text())[1]', 'VARCHAR(100)') as   SupplierNumber

FROM  @xml.nodes('order-headers/order-header') h(c)
CROSS APPLY h.c.nodes('order-lines/order-line') t(c);

db<>fiddle