我有以下 XML
`<?xml version="1.0" encoding="UTF-8"?>
<AX_PDM_DATA>
<FilePath>\\N...</FilePath>
<ArticleCategory>
<CategoryName>test</CategoryName>
<Article>
<ItemId>123</ItemId>
<StoppedList>
<StoppedStatus>0</StoppedStatus>
<StopDescriptionItem/>
</StoppedList>
<ECOList>
<ECOStatus>0</ECOStatus>
<ECODescription/>
</ECOList>
<NCList>
<NC>1</NC>
<NCnumber>NC19012836</NCnumber>
<NCCategory>ODR</NCCategory>
<NCSubCategory>LP</NCSubCategory>
<NCDescription>test</NCDescription>
</NCList>
</Article>
<Article>
<ItemId>1234</ItemId>
<StoppedList>
<StoppedStatus>1</StoppedStatus>
<StopDescriptionItem/>
</StoppedList>
<ECOList>
<ECOStatus>0</ECOStatus>
<ECODescription/>
</ECOList>
<NCList>
<NC>0</NC>
<NCnumber/>
<NCCategory/>
<NCSubCategory/>
<NCDescription/>
</NCList>
</Article>
<Article>
<ItemId>456</ItemId>
<StoppedList>
<StoppedStatus>1</StoppedStatus>
<StopDescriptionItem/>
</StoppedList>
<ECOList>
<ECOStatus>0</ECOStatus>
<ECODescription/>
</ECOList>
<NCList>
<NC>0</NC>
<NCnumber/>
<NCCategory/>
<NCSubCategory/>
<NCDescription/>
</NCList>
</Article>
<Article>
<ItemId>74.489</ItemId>
<StoppedList>
<StoppedStatus>1</StoppedStatus>
<StopDescriptionItem/>
</StoppedList>
<ECOList>
<ECOStatus>0</ECOStatus>
<ECODescription/>
</ECOList>
<NCList>
<NC>0</NC>
<NCnumber/>
<NCCategory/>
<NCSubCategory/>
<NCDescription/>
</NCList>
</Article>
<Article>
<ItemId>AB050</ItemId>
<StoppedList>
<StoppedStatus>0</StoppedStatus>
<StopDescriptionItem />
</StoppedList>
<ECOList>
<ECOStatus>1</ECOStatus>
<ECODescription>SDsdfgadfhadfhadh arfgadfadfh</ECODescription>
</ECOList>
<NCList>
<NC>1</NC>
<NCnumber>NC18005166</NCnumber>
<NCCategory>ODR</NCCategory>
<NCSubCategory>LP</NCSubCategory>
<NCDescription>check </NCDescription>
</NCList>
<NCList>
<NC>1</NC>
<NCnumber>NC18005205</NCnumber>
<NCCategory>ODR</NCCategory>
<NCSubCategory>LP</NCSubCategory>
<NCDescription>check2</NCDescription>
</NCList>
</Article>
</ArticleCategory>
</AX_PDM_DATA>
`
我想匯入到 SQL 資料庫中。在某些情況下，在 Item 節點下我們可以有多個 NClist 節點。在 NClist 中，我想將 NCnumbers 放入一列。現在我創建了以下 SQL 查詢：
`DECLARE @XmlFile XML
SELECT @XmlFile = BulkColumn
FROM OPENROWSET(BULK 'C:\temp\smallfile.xml', SINGLE_BLOB) x;
INSERT INTO GATEWAY_Table (ITEMID, STOPPEDSTATUS, STOPDESCRIPTIONITEM, ECOSTATUS, ECODESCRIPTION, NCNUMBER)
select
MY_XML.Item.query('ItemId').value('.', 'VARCHAR(20)'),
MY_XML.Item.query('StoppedList/StoppedStatus').value('.', 'VARCHAR(20)'),
MY_XML.Item.query('StoppedList/StopDescriptionItem').value('.', 'VARCHAR(max)'),
MY_XML.Item.query('ECOList/ECOStatus').value('.', 'VARCHAR(20)'),
MY_XML.Item.query('ECOList/ECODescription').value('.', 'VARCHAR(max)'),
XT2.NCLIST.query('NCList/NCnumber').value('.[1]', 'VARCHAR(max)')
FROM
@XMlfile.nodes('AX_PDM_DATA/ArticleCategory/Article') AS MY_XML(Item)
cross apply
item.nodes('NCList') as XT2(NCLIST)`
但我現在卡住了。誰能幫忙？
提前致謝
uj5u.com熱心網友回復：
請嘗試以下解決方案。
不用.query()方法，只要.value()方法就夠了。
SQL
DECLARE @GATEWAY_Table TABLE (
ITEMID VARCHAR(20),
STOPPEDSTATUS VARCHAR(20),
STOPDESCRIPTIONITEM VARCHAR(max),
ECOSTATUS VARCHAR(20),
ECODESCRIPTION VARCHAR(max),
NCNUMBER VARCHAR(max)
);
DECLARE @XmlFile XML;
SELECT @XmlFile = BulkColumn
FROM OPENROWSET(BULK 'e:\temp\smallfile.xml', SINGLE_BLOB) x;
INSERT INTO @GATEWAY_Table (ITEMID, STOPPEDSTATUS, STOPDESCRIPTIONITEM, ECOSTATUS, ECODESCRIPTION, NCNUMBER)
SELECT Item.value('(ItemId/text())[1]', 'VARCHAR(20)')
, Item.value('(StoppedList/StoppedStatus/text())[1]', 'VARCHAR(20)')
, Item.value('(StoppedList/StopDescriptionItem/text())[1]', 'VARCHAR(MAX)')
, Item.value('(ECOList/ECOStatus/text())[1]', 'VARCHAR(20)')
, Item.value('(ECOList/ECODescription/text())[1]', 'VARCHAR(MAX)')
, NCLIST.value('(NCnumber/text())[1]', 'VARCHAR(MAX)')
FROM @XMlfile.nodes('/AX_PDM_DATA/ArticleCategory/Article') AS t1(Item)
CROSS APPLY t1.Item.nodes('NCList') as t2(NCLIST);
-- test
SELECT * FROM @GATEWAY_Table;
輸出
-------- --------------- --------------------- ----------- ------------------------------- ------------
| ITEMID | STOPPEDSTATUS | STOPDESCRIPTIONITEM | ECOSTATUS | ECODESCRIPTION | NCNUMBER |
-------- --------------- --------------------- ----------- ------------------------------- ------------
| 123 | 0 | NULL | 0 | NULL | NC19012836 |
| 1234 | 1 | NULL | 0 | NULL | NULL |
| 456 | 1 | NULL | 0 | NULL | NULL |
| 74.489 | 1 | NULL | 0 | NULL | NULL |
| AB050 | 0 | NULL | 1 | SDsdfgadfhadfhadh arfgadfadfh | NC18005166 |
| AB050 | 0 | NULL | 1 | SDsdfgadfhadfhadh arfgadfadfh | NC18005205 |
-------- --------------- --------------------- ----------- ------------------------------- ------------
SQL #2
INSERT INTO @GATEWAY_Table (ITEMID, STOPPEDSTATUS, STOPDESCRIPTIONITEM, ECOSTATUS, ECODESCRIPTION, NCNUMBER)
SELECT Item.value('(ItemId/text())[1]', 'VARCHAR(20)')
, Item.value('(StoppedList/StoppedStatus/text())[1]', 'VARCHAR(20)')
, Item.value('(StoppedList/StopDescriptionItem/text())[1]', 'VARCHAR(MAX)')
, Item.value('(ECOList/ECOStatus/text())[1]', 'VARCHAR(20)')
, Item.value('(ECOList/ECODescription/text())[1]', 'VARCHAR(MAX)')
, Item.query('data(NCList/NCnumber)').value('.', 'VARCHAR(MAX)')
FROM @XMlfile.nodes('/AX_PDM_DATA/ArticleCategory/Article') AS t1(Item);
輸出
-------- --------------- --------------------- ----------- ------------------------------- -----------------------
| ITEMID | STOPPEDSTATUS | STOPDESCRIPTIONITEM | ECOSTATUS | ECODESCRIPTION | NCNUMBER |
-------- --------------- --------------------- ----------- ------------------------------- -----------------------
| 123 | 0 | NULL | 0 | NULL | NC19012836 |
| 1234 | 1 | NULL | 0 | NULL | |
| 456 | 1 | NULL | 0 | NULL | |
| 74.489 | 1 | NULL | 0 | NULL | |
| AB050 | 0 | NULL | 1 | SDsdfgadfhadfhadh arfgadfadfh | NC18005166 NC18005205 |
-------- --------------- --------------------- ----------- ------------------------------- -----------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/325206.html
標籤：sql sql-server xml 查询语句
下一篇：如果存在或如果存在則更新更有效