Parse an xml file without dups-CodePudding

How can I have the Attribute_ID in the column and Attribute_Value as value to be populated in the table. Below is XML doc:

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

I tried:

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

CodePudding user response：

Consider the following query based on that in your question...

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

Each column is querying the same Attribute_Value element, Tbl5.value('Attribute_Value[1]','VARCHAR(20)'), so this returns the following results:

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

To return values matching the output column names you need to restructure the XPath query to be like the following...

SELECT
  Tbl5.value('(Attribute[Attribute_ID="ALL0135"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL0135,
  Tbl5.value('(Attribute[Attribute_ID="ALL2306"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL2306,
  Tbl5.value('(Attribute[Attribute_ID="ALL2336"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL2336,
  Tbl5.value('(Attribute[Attribute_ID="ALL5742"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL5742,
  Tbl5.value('(Attribute[Attribute_ID="ALL5935"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL5935,
  Tbl5.value('(Attribute[Attribute_ID="ALL8220"]/Attribute_Value)[1]','VARCHAR(20)') AS ALL8220
FROM @xml.nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes') AS Tbl5(Tbl5);

Which returns the results:

ALL0135	ALL2306	ALL2336	ALL5742	ALL5935	ALL8220
000000002	000000000	000000000	000000000	000000000	000000044