我将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>
您需要将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<的在小提琴