将XML导入SQL中，并将多个节点合并为1列

Question

我有以下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:tempsmallfile.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)`

但是我现在卡住了。谁能帮忙?

Thanks in advance

Answer 1

请尝试以下解决方案。

不需要使用.query()方法，只要.value()方法就足够了。

/p>

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:tempsmallfile.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 |
+--------+---------------+---------------------+-----------+-------------------------------+-----------------------+