解析不带dup的xml文件

Question

如何将列中的Attribute_ID和Attribute_Value作为值填充到表中。下面是XML文档:

<ExperianBureauData>
<NetConnectResponse>
<CompletionCode>0000</CompletionCode>
<Products>
<CreditProfile>
<ARF_Report> 
<n836_-_Premier_Attributes>
<Record_ID>836</Record_ID>
<Record_Length>314</Record_Length>
<Message_Code>1</Message_Code>
<Attribute>
<Attribute_ID>ALL0135</Attribute_ID>
<Attribute_Value>000000002</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL2306</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL2336</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL5742</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL5935</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL8220</Attribute_ID>
<Attribute_Value>000000044</Attribute_Value>
</Attribute>            
</n836_-_Premier_Attributes>         
</ARF_Report>
</CreditProfile>
</Products>
</NetConnectResponse>
</ExperianBureauData> 

我试着:

SELECT DISTINCT       
-- -- -- -- <<n836_-_Premier_Attributes>/<Attribute>/
-- -- -- -- Tbl7
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL0135 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2306 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2336 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL5742  
FROM [dbo].[STG_XML_TstTbl]
CROSS APPLY [XMLReport].nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute') AS Tbl5(Tbl5);

Answer 1

根据您的问题考虑以下查询…

declare @xml xml = N' <ExperianBureauData>
<NetConnectResponse>
<CompletionCode>0000</CompletionCode>
<Products>
<CreditProfile>
<ARF_Report> 
<n836_-_Premier_Attributes>
<Record_ID>836</Record_ID>
<Record_Length>314</Record_Length>
<Message_Code>1</Message_Code>
<Attribute>
<Attribute_ID>ALL0135</Attribute_ID>
<Attribute_Value>000000002</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL2306</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL2336</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL5742</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL5935</Attribute_ID>
<Attribute_Value>000000000</Attribute_Value>
</Attribute>
<Attribute>
<Attribute_ID>ALL8220</Attribute_ID>
<Attribute_Value>000000044</Attribute_Value>
</Attribute>            
</n836_-_Premier_Attributes>         
</ARF_Report>
</CreditProfile>
</Products>
</NetConnectResponse>
</ExperianBureauData>';
SELECT DISTINCT       
Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL0135 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2306 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2336 
,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL5742  
FROM @xml.nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute') AS Tbl5(Tbl5);

每一列查询相同的Attribute_Value元素Tbl5.value('Attribute_Value[1]','VARCHAR(20)')，因此返回以下结果:

tbody> <<tr>

ALL0135	ALL2306	ALL2336	ALL5742
000000000	000000000	000000000	000000000
000000002	000000002	000000002	000000002
000000044	000000044	000000044	000000044