sql - 如何以逗号 (,) 字符分隔的字符串形式返回 XML 嵌套节点的行
问题描述
在下面的查询中需要帮助以返回以下 XML 嵌套节点的行。某些列的数据需要返回多个值（存在于 XML 脚本中），以逗号分隔，例如节点“BillType”、“BillNumber”、“CONTAINER_NUMBER”或“CONTAINER_STATUS”等。
提前致谢。
XML...
DECLARE @xml xml
set @xml = N'<declaration xmlns="http://xyz.IND/NCCS/v2">
<identification>77988324</identification>
<type>I10</type>
<functionalReference>IM15_262053</functionalReference>
<transactionType>9</transactionType>
<totalGrossMass>
<quantity>992</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</totalGrossMass>
<jurisdiction>
<importDate>20150822</importDate>
</jurisdiction>
<submitter>
<identifier>40063605C</identifier>
</submitter>
<permit>
<authority>DOC</authority>
<number>12345</number>
<sequence>1</sequence>
</permit>
<permit>
<authority>LTS</authority>
<number>4321</number>
<sequence>2</sequence>
</permit>
<permit>
<authority>AF1</authority>
<number>43253445</number>
<sequence>3</sequence>
</permit>
<additionalDocument>
<type>CER</type>
<identifier>cer1</identifier>
<sequence>4</sequence>
</additionalDocument>
<additionalDocument>
<type>PP</type>
<identifier>pp1</identifier>
<sequence>5</sequence>
</additionalDocument>
<additionalInformation>
<code>AWC</code>
<type>OIN</type>
<sequence>5</sequence>
</additionalInformation>
<additionalInformation>
<code>CGO</code>
<type>OIN</type>
<sequence>6</sequence>
</additionalInformation>
<additionalInformation>
<code>DIP</code>
<type>OIN</type>
<sequence>7</sequence>
</additionalInformation>
<additionalInformationALP>
<text>PLEASE LET IT PASS</text>
<code>Y</code>
<sequence>1</sequence>
</additionalInformationALP>
<additionalInformationHAN>
<text>HANDLE</text>
<sequence>3</sequence>
</additionalInformationHAN>
<additionalInformationMAC>
<accountNumber>123456</accountNumber>
<accountName>Name</accountName>
<sequence>4</sequence>
</additionalInformationMAC>
<agent>
<identifier>40063605C</identifier>
<roleCode>CB</roleCode>
</agent>
<approvedEstablishmentPlace>
<identifier>7129D</identifier>
</approvedEstablishmentPlace>
<approvedEstablishmentPlace>
<identifier>7174K</identifier>
</approvedEstablishmentPlace>
<borderTransportMeans>
<identifier>VHQZ123</identifier>
<name>S.S. KUMAR</name>
<transportModeCode>1</transportModeCode>
<voyageNumber>138</voyageNumber>
</borderTransportMeans>
<carrier>
<name>IND CARRIER</name>
</carrier>
<currencyExchange>
<rateOfExchange>
<value>1.2</value>
<sequence>1</sequence>
</rateOfExchange>
<currencyCode>AUD</currencyCode>
</currencyExchange>
<currencyExchange>
<rateOfExchange>
<value>2.3</value>
<sequence>2</sequence>
</rateOfExchange>
<currencyCode>USD</currencyCode>
</currencyExchange>
<paymentMethod>
<paymentMethodCode>C</paymentMethodCode>
<sequence>1</sequence>
</paymentMethod>
<dutyTaxFee>
<amount>3</amount>
<currencyCode>AU</currencyCode>
<typeCode>ADD</typeCode>
<sequence>2</sequence>
</dutyTaxFee>
<GST>
<amount>1</amount>
<currencyCode>AU</currencyCode>
<sequence>4</sequence>
</GST>
<tariffDuty>
<amount>2</amount>
<currencyCode>AU</currencyCode>
<sequence>3</sequence>
</tariffDuty>
<totalAmount>
<amount>6</amount>
<currencyCode>AU</currencyCode>
<sequence>5</sequence>
</totalAmount>
<goodsShipment>
<exportationCountryCode>AU</exportationCountryCode>
<transactionNatureCode>41</transactionNatureCode>
<consignment>
<loadingLocation>AUQBE</loadingLocation>
<bill>
<identifier>MB1</identifier>
<type>MB</type>
</bill>
<bill>
<identifier>HB1</identifier>
<type>BM</type>
<container>
<identifier>ABC128021</identifier>
<sequence>1</sequence>
<packageSequence>1</packageSequence>
</container>
</bill>
<bill>
<identifier>HB2</identifier>
<type>BM</type>
<container>
<identifier>CSCU7549505</identifier>
<sequence>2</sequence>
<packageSequence>3</packageSequence>
<packageSequence>4</packageSequence>
</container>
<packageSequence>2</packageSequence>
</bill>
<transportEquipment>
<sequence>1</sequence>
<additionalInformationMAS>
<approvedSystemNumber>123456</approvedSystemNumber>
<sequence>2</sequence>
</additionalInformationMAS>
<additionalInformationMCD>
<answers>YNYNY</answers>
<sequence>1</sequence>
</additionalInformationMCD>
<characteristicsCode>45</characteristicsCode>
<fullness>4</fullness>
<identifier>ABC128021</identifier>
<seal>
<sequence>1</sequence>
<identifier>seal1</identifier>
</seal>
<seal>
<sequence>2</sequence>
<identifier>seal2</identifier>
</seal>
<containerPackLocation>
<name>1</name>
</containerPackLocation>
</transportEquipment>
<transportEquipment>
<sequence>2</sequence>
<additionalInformationMAS>
<approvedSystemNumber>987456</approvedSystemNumber>
<sequence>4</sequence>
</additionalInformationMAS>
<additionalInformationMCD>
<answers>YYNNN</answers>
<sequence>3</sequence>
</additionalInformationMCD>
<characteristicsCode>43</characteristicsCode>
<fullness>5</fullness>
<identifier>CSCU7549505</identifier>
<seal>
<sequence>1</sequence>
<identifier>seal22</identifier>
</seal>
<seal>
<sequence>2</sequence>
<identifier>seal23</identifier>
</seal>
</transportEquipment>
<unloadingLocation>
<identifier>AUSYD</identifier>
</unloadingLocation>
</consignment>
<freightCharge>
<amount>12</amount>
<currencyCode>AU</currencyCode>
<apportionmentCode>160</apportionmentCode>
</freightCharge>
<deliveryDestination>
<name>ACME DELIVERY</name>
<address>
<city>dtp</city>
<countryCode>AU</countryCode>
<line>dtp</line>
<postcode>2500</postcode>
</address>
</deliveryDestination>
<governmentAgencyGoodsItem>
<sequence>1</sequence>
<customsValue>
<amount>1</amount>
<currencyCode>AU</currencyCode>
</customsValue>
<permit>
<authority>MOH</authority>
<number>ga2</number>
<sequence>2</sequence>
</permit>
<additionalInformation>
<code>CGO</code>
<type>OIN</type>
<sequence>6</sequence>
</additionalInformation>
<additionalInformationPRO>
<code>BEF</code>
<sequence>4</sequence>
</additionalInformationPRO>
<additionalInformationREL>
<relationshipIndicatorCode>135</relationshipIndicatorCode>
<supplierSequence>1</supplierSequence>
<sequence>7</sequence>
</additionalInformationREL>
<approvedEstablishmentPlace>
<identifier>10063</identifier>
</approvedEstablishmentPlace>
<commodity>
<goodsDescription>goods desc</goodsDescription>
<lotNumber>lot</lotNumber>
<productBestBefore>20131101</productBestBefore>
<value>
<amount>13</amount>
<currencyCode>AUD</currencyCode>
</value>
<intendedUse>
<code>AC</code>
<description>I intend to use it</description>
</intendedUse>
<tariffItem>
<code>3926109001K</code>
<sequence>1</sequence>
</tariffItem>
<constituent>
<quantity>10</quantity>
<name>peanuts</name>
</constituent>
<dutyTaxFeeRegime>
<code>AU</code>
<sequence>1</sequence>
</dutyTaxFeeRegime>
<GST>
<amount>12</amount>
<currencyCode>AU</currencyCode>
<sequence>2</sequence>
</GST>
<tariffDuty>
<amount>12</amount>
<currencyCode>AU</currencyCode>
<sequence>3</sequence>
</tariffDuty>
<grower>
<name>grower</name>
<address>
<city>city</city>
<countryCode>AD</countryCode>
<line>grower</line>
<postcode>jk</postcode>
</address>
</grower>
<itinerary>
<sequence>1</sequence>
<countryCode>AU</countryCode>
</itinerary>
<itinerary>
<sequence>2</sequence>
<countryCode>FI</countryCode>
</itinerary>
<itinerary>
<sequence>3</sequence>
<countryCode>AF</countryCode>
</itinerary>
<manufacturer>
<name>manufacturer</name>
<address>
<city>jkldsj</city>
<countryCode>PA</countryCode>
<line>jdskljl</line>
<postcode>jdklsja</postcode>
</address>
</manufacturer>
<producer>
<name>producer</name>
<address>
<city>dkasjk</city>
<countryCode>AD</countryCode>
<line>producer</line>
<postcode>j</postcode>
</address>
</producer>
<product>
<identifier>mc1</identifier>
<commodityIdentifierType>MC</commodityIdentifierType>
</product>
<productName>
<name>brand</name>
<type>223</type>
</productName>
<productCharacteristics>
<code>Y</code>
<type>211</type>
</productCharacteristics>
<productCharacteristics>
<code>Y</code>
<type>61</type>
</productCharacteristics>
<source>AU</source>
<temperature>
<storageRequirement>
<quantity>10</quantity>
<unitOfMeasurement>CEL</unitOfMeasurement>
</storageRequirement>
<minimumStorageRequirement>
<quantity>-2</quantity>
<unitOfMeasurement>CEL</unitOfMeasurement>
</minimumStorageRequirement>
</temperature>
<transportEquipment>
<sequence>1</sequence>
<identifier>ABC128021</identifier>
</transportEquipment>
</commodity>
<examinationPlace>
<name>Treatment Provider</name>
</examinationPlace>
<goodsMeasure>
<grossWeight>
<quantity>12</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</grossWeight>
<netNetWeight>
<quantity>10</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</netNetWeight>
<tariff>
<quantity>12</quantity>
<unitOfMeasurement>NCL</unitOfMeasurement>
</tariff>
</goodsMeasure>
<goodsMeasureSupplementary>
<tariff>
<quantity>1</quantity>
<unitOfMeasurement>KGM</unitOfMeasurement>
</tariff>
</goodsMeasureSupplementary>
<origin>
<countryCode>AU</countryCode>
<state>NSW</state>
</origin>
<packaging>
<sequence>1</sequence>
<marksNumbers>marks</marksNumbers>
<quantity>1</quantity>
<type>FI</type>
<packingMaterialDescription>wood</packingMaterialDescription>
<volume>
<quantity>12</quantity>
<unitOfMeasurement>MTQ</unitOfMeasurement>
</volume>
</packaging>
<packaging>
<sequence>2</sequence>
<marksNumbers>marks</marksNumbers>
<quantity>1</quantity>
<type>FI</type>
<packingMaterialDescription>wood</packingMaterialDescription>
<volume>
<quantity>12</quantity>
<unitOfMeasurement>MTQ</unitOfMeasurement>
</volume>
</packaging>
<packaging>
<sequence>3</sequence>
<marksNumbers>marks</marksNumbers>
<quantity>1</quantity>
<type>FI</type>
<packingMaterialDescription>wood</packingMaterialDescription>
<volume>
<quantity>12</quantity>
<unitOfMeasurement>MTQ</unitOfMeasurement>
</volume>
</packaging>
<packaging>
<sequence>4</sequence>
<marksNumbers>marks</marksNumbers>
<quantity>1</quantity>
<type>FI</type>
<packingMaterialDescription>wood</packingMaterialDescription>
<volume>
<quantity>12</quantity>
<unitOfMeasurement>MTQ</unitOfMeasurement>
</volume>
</packaging>
<commissionsValuation>
<adjustment>
<amount>3</amount>
<currencyCode>AU</currencyCode>
</adjustment>
<sequence>3</sequence>
</commissionsValuation>
<freightValuation>
<adjustment>
<amount>10</amount>
<currencyCode>AU</currencyCode>
</adjustment>
<sequence>1</sequence>
</freightValuation>
<insuranceValuation>
<adjustment>
<amount>11</amount>
<currencyCode>AU</currencyCode>
</adjustment>
<sequence>2</sequence>
</insuranceValuation>
<royaltiesValuation>
<adjustment>
<amount>5</amount>
<currencyCode>AU</currencyCode>
</adjustment>
<sequence>4</sequence>
</royaltiesValuation>
</governmentAgencyGoodsItem>
<invoice>
<identifier>INVOICE1</identifier>
<incoterms>CPT</incoterms>
<sequence>1</sequence>
</invoice>
<invoice>
<identifier>INVOICE2</identifier>
<incoterms>CFR</incoterms>
<sequence>2</sequence>
</invoice>
<deliveryNotificationParty>
<name>NP1</name>
<communication>
<value>SSMAIL</value>
<type>EM</type>
</communication>
</deliveryNotificationParty>
<seller>
<name>SELLERNAME</name>
<address>
<city>SELLER</city>
<countryCode>AU</countryCode>
<line>SELLER</line>
<postcode>SD</postcode>
</address>
</seller>
<seller>
<name>SELLER2</name>
<address>
<city>city2</city>
<countryCode>YE</countryCode>
<line>seller2</line>
<postcode>jkh</postcode>
</address>
</seller>
<stuffingEstablishment>
<name>vp1</name>
<address>
<city>vp</city>
<countryCode>KE</countryCode>
<line>vp1a</line>
<postcode>00002</postcode>
</address>
</stuffingEstablishment>
<stuffingEstablishment>
<name>vp2</name>
<address>
<city>djskljdklajkl</city>
<countryCode>LA</countryCode>
<line>vp2add</line>
<postcode>999</postcode>
</address>
</stuffingEstablishment>
<supplier>
<identifier>40038489J</identifier>
<contact>
<name>MAGGS</name>
<communication>
<value>xyz@gmail.com</value>
<type>EM</type>
</communication>
</contact>
<sequence>1</sequence>
</supplier>
<supplier>
<identifier>00710842W</identifier>
<contact>
<name>maggs2</name>
<communication>
<value>phone2</value>
<type>TE</type>
</communication>
</contact>
<sequence>2</sequence>
</supplier>
<warehouse>
<identifier>20591</identifier>
</warehouse>
</goodsShipment>
<importer>
<identifier>40063605C</identifier>
<address>
<city>IMporterCity</city>
<countryCode>AT</countryCode>
<line>importer</line>
<postcode>2500</postcode>
</address>
<contact>
<name>Maggs</name>
<communication>
<value>XYZ@gmail.com</value>
<type>EM</type>
</communication>
</contact>
</importer>
<previousDocument>
<identifier>123456</identifier>
<type>I51</type>
</previousDocument>
<previousDocument>
<identifier>6987458</identifier>
<type>I52</type>
</previousDocument>
<packaging>
<sequence>1</sequence>
<quantity>2</quantity>
<type>AE</type>
</packaging>
<packaging>
<sequence>2</sequence>
<quantity>18</quantity>
<type>PK</type>
</packaging>
<packaging>
<sequence>3</sequence>
<quantity>29</quantity>
<type>FI</type>
</packaging>
<packaging>
<sequence>4</sequence>
<quantity>44</quantity>
<type>FI</type>
</packaging>
<remarks>
<text>CAUSE IT CAN</text>
<sequence>2</sequence>
</remarks>
<declarant>
<identifier>40048559D</identifier>
<communication>
<value>12398120</value>
<type>AL</type>
</communication>
<communication>
<value>XYZ@gmail.com</value>
<type>EM</type>
</communication>
</declarant>
<TSWExtension>
<MIGVersion>V1.1</MIGVersion>
</TSWExtension>
</declaration>'
询问...
;WITH XMLNAMESPACES (DEFAULT 'http://xyz.IND/NCCS/v2')
SELECT response.value('(jurisdiction/importDate/text())[1]', 'varchar(50)') AS DATE_OF_IMPORT,
response.value('(exitDate/text())[1]', 'varchar(50)') AS DATE_OF_EXPORT,
response.value('(borderTransportMeans/name/text())[1]', 'varchar(100)') AS CRAFT_NAME,
response.value('(borderTransportMeans/flightNumber/text())[1]', 'varchar(50)') AS FLIGHT_NUMBER,
response.value('(borderTransportMeans/voyageNumber/text())[1]', 'varchar(50)') AS VOYAGE_NUMBER,
response.value('(goodsShipment/consignment/loadingLocation/text())[1]', 'varchar(50)') AS PORT_OF_LOADING,
response.value('(goodsShipment/consignment/unloadingLocation/identifier/text())[1]', 'varchar(50)') AS PORT_OF_DISCHARGE,
response.value('(goodsShipment/consignment/bill/type/text())[1]', 'varchar(50)') AS BILL_TYPE,
response.value('(goodsShipment/consignment/bill/identifier/text())[1]', 'varchar(50)') AS BILL_NUMBER,
response.value('(goodsShipment/consignment/transportEquipment/identifier/text())[1]', 'varchar(100)') AS CONTAINER_NUMBER,
response.value('(goodsShipment/consignment/transportEquipment/fullness/text())[1]', 'varchar(100)') AS CONTAINER_STATUS,
response.value('(goodsShipment/consignment/transportEquipment/seal/identifier/text())[1]', 'varchar(100)') AS CONTAINER_SEAL,
response.value('(goodsShipment/governmentAgencyGoodsItem/packaging/quantity/text())[1]', 'varchar(100)') AS IND_NUMBER_OF_PACKAGES,
response.value('(goodsShipment/governmentAgencyGoodsItem/packaging/type/text())[1]', 'varchar(100)') AS IND_TYPE_OF_PACKAGES,
response.value('(packaging/quantity/text())[1]', 'varchar(100)') AS NUMBER_OF_PACKAGES,
response.value('(packaging/type/text())[1]', 'varchar(100)') AS TYPE_OF_PACKAGES,
response.value(
'(goodsShipment/governmentAgencyGoodsItem/goodsMeasure/grossWeight/quantity/text())[1]', 'varchar(50)') AS GROSS_WEIGHT,
response.value('(goodsShipment/deliveryNotificationParty/identifier/text())[1]', 'varchar(50)') AS GOODSSHIPMENT_DNP_IDENTIFIER,
response.value('(goodsShipment/deliveryNotificationParty/communication/value/text())[1]', 'varchar(100)') AS GOODSSHIPMENT_DNP_EMAIL,
response.value('(carrier/name/text())[1]', 'varchar(100)') AS CARRIER_NAME,
response.value('(goodsShipment/consignment/goodsLocation/identifier/text())[1]', 'varchar(100)') AS LOCATION_OF_GOODS
FROM @xml.nodes('/declaration') xmltable(response);
解决方案
将账单信息汇总为 CSVtype(identifier)
WITH XMLNAMESPACES (DEFAULT 'http://xyz.IND/NCCS/v2')
SELECT response.value('(jurisdiction/importDate/text())[1]', 'varchar(50)') AS DATE_OF_IMPORT,
...
bt.Bills,
tt.Transp,
...
FROM @xml.nodes('/declaration') xmltable(response)
CROSS APPLY (
SELECT
string_agg(
bt.n.value('(type/text())[1]', 'varchar(50)')
+ '('
+ bt.n.value('(identifier/text())[1]', 'varchar(50)')
+ ')'
, ',') AS Bills
FROM xmltable.response.nodes('goodsShipment/consignment/bill') bt(n)
) bt
CROSS APPLY (
SELECT
string_agg(
tt.n.value('(identifier/text())[1]', 'varchar(50)'), ',') transp
FROM xmltable.response.nodes('goodsShipment/consignment/transportEquipment') tt(n)
) tt;
推荐阅读
- node.js - 我无法读取 Firebase Functions 上的数据
- javascript - 如何从 React-Navigation v3 中的 navigationOptions 将导航对象传递到侧面板？
- html - 如何使调整大小属性与 flexBox 一起使用？
- android - android Service 类的 kotlin 类 getter 中的溢出堆栈大小
- python - 获取 DateTimeIndex 周期的平均值，然后将它们重新分配到原始数据框列
- akka - 如何等到童星活跃？
- c++ - 为什么是真、假和空指针纯右值？
- electron - 如何使电子重建工作以便我可以使用 sqlite3？
- matlab - 在 CVX (Matlab) 中使用线性约束时出错
- jupyter-notebook - 如何将物理文件路径添加到 os.path.join？