Import XML into SQL and combine multiple nodes into 1 column-CodePudding

I'm having the following 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>
`

which I want to import into a SQL database. In some circumstances under the Item node we can have multiple NClist nodes. In the NClist I have NCnumbers which I want to have into a single column. Now I created the following SQL query:

`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)`

But I'm stuck now. who can help?

Thanks in advance

CodePudding user response：

Please try the following solution.

No need to use the .query() method, just the .value() method is enough.

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;

Output

 -------- --------------- --------------------- ----------- ------------------------------- ------------ 
| 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);

Output

 -------- --------------- --------------------- ----------- ------------------------------- ----------------------- 
| 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 |
 -------- --------------- --------------------- ----------- ------------------------------- -----------------------