Problem reading all Attributes into SQL table

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    Here is an example
    😎


    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @TXML XML = '<ACES version="3.0">
    <Header>
      <Company>AI-CARDONE-CARDONE SELECT</Company>
      <SenderName>WHI SOLUTIONS</SenderName>
      <MfrCode>A2L</MfrCode>
      <DocumentTitle>AI-CARDONE-CARDONE SELECT</DocumentTitle>
      <EffectiveDate>2018-11-06</EffectiveDate>
      <SubmissionType>FULL</SubmissionType>
    </Header>
    <App action="A" id="1">
      <BaseVehicle id="1261" />
      <EngineBase id="213" />
      <Qual id="2229">
      <text>Contains Cap &amp; Rotor</text>
      </Qual>
      <Qty>1</Qty>
      <PartType id="7108" />
      <MfrLabel>New Distributor (Electronic)</MfrLabel>
      <Part>2507-317771</Part>
    </App>
    <App action="A" id="2">
      <BaseVehicle id="1262" />
      <EngineBase id="213" />
      <Qual id="2229">
      <text>Contains Cap &amp; Rotor</text>
      </Qual>
      <Qty>1</Qty>
      <PartType id="7108" />
      <MfrLabel>New Distributor (Electronic)</MfrLabel>
      <Part>2507-317771</Part>
    </App>
    </ACES>';

    SELECT
      HDR.DATA.value('(Company/text())[1]','VARCHAR(50)')
     ,HDR.DATA.value('(SenderName/text())[1]','VARCHAR(50)')
     ,HDR.DATA.value('(MfrCode/text())[1]','VARCHAR(50)')
     ,HDR.DATA.value('(DocumentTitle/text())[1]','VARCHAR(50)')
     ,HDR.DATA.value('(EffectiveDate/text())[1]','DATE')
     ,HDR.DATA.value('(SubmissionType/text())[1]','VARCHAR(50)')
     ,APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'    ) AS BaseVehicle
     ,APP.DATA.value('(Qty/text())[1]'   ,'INT'    ) AS Qty
     ,APP.DATA.value('(PartType/@id)[1]'  ,'INT'    ) AS PartType
     ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
     ,APP.DATA.value('(Part/text())[1]'   ,'varchar(50)' ) AS Part
    FROM   @TXML.nodes('ACES/Header') HDR(DATA)
    CROSS APPLY @TXML.nodes('ACES/App')  APP(DATA);

  • md44

    SSC Veteran

    Points: 210

    Thank you again!  FYI, that didnt work, but I modified it to include a 2nd cross apply join which looks OK (the results and record count are accurate).  I'm hoping this will work and dupe any records.

    select HDR.DATA.value('(Company/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(SenderName/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(MfrCode/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(DocumentTitle/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(EffectiveDate/text())[1]','DATE')
    ,HDR.DATA.value('(SubmissionType/text())[1]','VARCHAR(50)')
    ,APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'  ) AS BaseVehicle
    ,APP.DATA.value('(Qty/text())[1]'  ,'INT'  ) AS Qty
    ,APP.DATA.value('(PartType/@id)[1]' ,'INT'  ) AS PartType
    ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
    ,APP.DATA.value('(Part/text())[1]' ,'varchar(50)' ) AS Part
    from XMLStagingTABLE TX
    CROSS APPLY TX.XML_DATA.nodes('ACES/Header') HDR(DATA)
    CROSS APPLY TX.XML_DATA.nodes('ACES/App') APP(DATA);

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    md44 - Thursday, December 20, 2018 3:27 AM

    Thank you again!  FYI, that didnt work, but I modified it to include a 2nd cross apply join which looks OK (the results and record count are accurate).  I'm hoping this will work and dupe any records.

    select HDR.DATA.value('(Company/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(SenderName/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(MfrCode/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(DocumentTitle/text())[1]','VARCHAR(50)')
    ,HDR.DATA.value('(EffectiveDate/text())[1]','DATE')
    ,HDR.DATA.value('(SubmissionType/text())[1]','VARCHAR(50)')
    ,APP.DATA.value('(BaseVehicle/@id)[1]' ,'INT'  ) AS BaseVehicle
    ,APP.DATA.value('(Qty/text())[1]'  ,'INT'  ) AS Qty
    ,APP.DATA.value('(PartType/@id)[1]' ,'INT'  ) AS PartType
    ,APP.DATA.value('(MfrLabel/text())[1]' ,'varchar(50)' ) AS MfrLabel
    ,APP.DATA.value('(Part/text())[1]' ,'varchar(50)' ) AS Part
    from XMLStagingTABLE TX
    CROSS APPLY TX.XML_DATA.nodes('ACES/Header') HDR(DATA)
    CROSS APPLY TX.XML_DATA.nodes('ACES/App') APP(DATA);

    Of course it didn't work on the table query as the example uses an XML variable😉
    😎

  • md44

    SSC Veteran

    Points: 210

    Hello Again.  I'm having a similar problem with a differnt xml file where I again need to import Header and Child info and I'm not getting any records returned.  If you can please guide me in how to construct the query, I would really appreciate.

    <PIES xmlns="http://www.aftermarket.org" xmlns:xs="http://www.w3.org/2001/XMLSchema"><Header><PIESVersion>6.5</PIESVersion><SubmissionType>FULL</SubmissionType><BlanketEffectiveDate>2018-11-07</BlanketEffectiveDate><ParentAAIAID>BDQB</ParentAAIAID><BrandOwnerAAIAID>BBKP</BrandOwnerAAIAID><CurrencyCode>USD</CurrencyCode><LanguageCode>EN</LanguageCode></Header><Items><Item MaintenanceType="C"><BaseItemID>07659</BaseItemID><PartNumber>07659</PartNumber><BrandAAIAID>BBKP</BrandAAIAID><BrandLabel>Chilton</BrandLabel><SubBrandAAIAID/><DigitalAssets><DigitalFileInformation MaintenanceType="C" LanguageCode="EN"><FileName>07659_V22A03R1.jpg</FileName><AssetType>P04</AssetType><FileType>JPG</FileType><URI>https://images.wrenchead.com/smartpages/partinfo_resize/CHI/07659_V22A03R1.jpg</URI><FileDateModified>2018-10-01</FileDateModified><Country>US</Country></DigitalFileInformation></DigitalAssets></Item><Item MaintenanceType="C"><BaseItemID>07891</BaseItemID><PartNumber>07891</PartNumber><BrandAAIAID>BBKP</BrandAAIAID><BrandLabel>Chilton</BrandLabel><SubBrandAAIAID/><DigitalAssets><DigitalFileInformation MaintenanceType="C" LanguageCode="EN"><FileName>07891_V22A03R1.jpg</FileName><AssetType>P04</AssetType><FileType>JPG</FileType><URI>https://images.wrenchead.com/smartpages/partinfo_resize/CHI/07891_V22A03R1.jpg</URI><FileDateModified>2018-10-01</FileDateModified><Country>US</Country></DigitalFileInformation></DigitalAssets></Item><Item MaintenanceType="C"><BaseItemID>09123</BaseItemID><PartNumber>09123</PartNumber><BrandAAIAID>BBKP</BrandAAIAID><BrandLabel>Chilton</BrandLabel><SubBrandAAIAID/><DigitalAssets><DigitalFileInformation MaintenanceType="C" LanguageCode="EN"><FileName>09123_V22A03R1.jpg</FileName><AssetType>P04</AssetType><FileType>JPG</FileType><URI>https://images.wrenchead.com/smartpages/partinfo_resize/CHI/09123_V22A03R1.jpg</URI><FileDateModified>2018-10-01</FileDateModified><Country>US</Country></DigitalFileInformation></DigitalAssets></Item&gt;

    Example query is again returning no results:

        SELECT
            HDR.DATA.value('(ParentAAIAID/text())[1]','VARCHAR(100)')as ParentAAIAID
            ,HDR.DATA.value('(BrandOwnerAAIAID/text())[1]','VARCHAR(100)')as BrandOwnerAAIAID

            ,APP.DATA.value('(BaseItemID/@id)[1]' ,'varchar(50)'  ) AS BaseItemID
            ,APP.DATA.value('(PartNumber/@id)[1]' ,'varchar(50)'  ) AS PartNumber
    from XMLStagingTABLE_Pies TX
    CROSS APPLY TX.XML_DATA.nodes('PIES/Header') HDR(DATA)
    CROSS APPLY TX.XML_DATA.nodes('PIES/Items') APP(DATA);

  • drew.allen

    SSC Guru

    Points: 76493

    There are three issues with your query.

    1)  Your XML uses a namespace, and you have not adjusted your query to account for that.  The easiest way to do this is to use a wildcard for the namespace, e.g., (*:PIES instead of just PIES).
    2)  Your path for the APP data is incorrect.  You have PIES/Items, when it should be *:PIES/*:Items/*:Item.
    3)  You are asking for an attribute value when the data is an element value.  There is a big difference between <BaseItemID id="07659" /> and <BaseItemID>07659</BaseItemID>.  Your query assumes that the data is in the first form, when your data is actually in the second form.

    Drew

    PS: I forgot to mention that your XML is malformed.  You're missing some ending tags "...</Items></PIES>"

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • md44

    SSC Veteran

    Points: 210

    Thank you for the reply.  Yes I accidentally copied just the first few rows which is why the ending tags are gone.  I followed your advice on adding wildcards, but SQL is returning:

    : No more tokens expected at the end of the XQuery expression. Found 'IES'.

    Would you mind typing it into my query and posting?  Would truly appreciate.

    Thank you again for the help.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    md44 - Wednesday, December 26, 2018 10:13 AM

    Thank you for the reply.  Yes I accidentally copied just the first few rows which is why the ending tags are gone.  I followed your advice on adding wildcards, but SQL is returning:

    : No more tokens expected at the end of the XQuery expression. Found 'IES'.

    Would you mind typing it into my query and posting?  Would truly appreciate.

    Thank you again for the help.

    Please tell me you actually types PIES not IES when writing the wildcard specification.

    Do a QUOTED REPLY of Drews post to see what he had actually typed.

  • Lynn Pettis

    SSC Guru

    Points: 442118

    This is what you should have typed minus the space between the : (colon) and the P:  *: PIES/*:Items/*:Item

  • md44

    SSC Veteran

    Points: 210

    Hi.  Below is the query I'm using but still getting no results 🙁
        SELECT
            HDR.DATA.value('(ParentAAIAID/text())[1]','VARCHAR(100)')as ParentAAIAID
            ,HDR.DATA.value('(BrandOwnerAAIAID/text())[1]','VARCHAR(100)')as BrandOwnerAAIAID

            ,APP.DATA.value('(BaseItemID/@id)[1]' ,'varchar(50)'  ) AS BaseItemID
            ,APP.DATA.value('(PartNumber/@id)[1]' ,'varchar(50)'  ) AS PartNumber
    from XMLStagingTABLE_Pies TX
    CROSS APPLY TX.XML_DATA.nodes('PIES/Header') HDR(DATA)
    CROSS APPLY TX.XML_DATA.nodes('PIES/*:Items/*:Item') APP(DATA)

  • drew.allen

    SSC Guru

    Points: 76493

    md44 - Wednesday, December 26, 2018 12:09 PM

    Hi.  Below is the query I'm using but still getting no results 🙁
        SELECT
            HDR.DATA.value('(ParentAAIAID/text())[1]','VARCHAR(100)')as ParentAAIAID
            ,HDR.DATA.value('(BrandOwnerAAIAID/text())[1]','VARCHAR(100)')as BrandOwnerAAIAID

            ,APP.DATA.value('(BaseItemID/@id)[1]' ,'varchar(50)'  ) AS BaseItemID
            ,APP.DATA.value('(PartNumber/@id)[1]' ,'varchar(50)'  ) AS PartNumber
    from XMLStagingTABLE_Pies TX
    CROSS APPLY TX.XML_DATA.nodes('PIES/Header') HDR(DATA)
    CROSS APPLY TX.XML_DATA.nodes('PIES/*:Items/*:Item') APP(DATA)

    1) If you are using namespaces, which you are, and you haven't specified a default namespace, which you haven't, you need to supply a name space for EVERY SINGLE ELEMENT.
    2)  You have only partially fixed the path for APP, because you haven't supplied the namespace for every element.
    3)  You are still looking for a value in an attribute that doesn't exist.  You need to be looking for the value in the element itself.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • md44

    SSC Veteran

    Points: 210

    Ok, thanks for the reply.  I realize it's not the way it should be which is why I am asking for help here.  I haven't been able to figure out the proper way to lay out this query.  Any help would be much appreciated.  Thank you.

  • drew.allen

    SSC Guru

    Points: 76493

    md44 - Wednesday, December 26, 2018 5:26 PM

    Ok, thanks for the reply.  I realize it's not the way it should be which is why I am asking for help here.  I haven't been able to figure out the proper way to lay out this query.  Any help would be much appreciated.  Thank you.

    You already have enough information to figure this out on your own.  You learn much better if you figure it for yourself than having it spoon-fed to you, as evidenced by the fact that you are still repeating the same exact mistakes that were corrected earlier in this thread.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • md44

    SSC Veteran

    Points: 210

    Below is the XML that I'm using.  I'm having 2 problems:

    Problem 1- I can't figure out how to query the data witht the namespace in the document even with the use of wildcards.  If I REMOVE the namespace line then I can  get to the data but I need to leave it in as I'll be looping through thousands of these types of files and need to use the wildcards so I'm able to query.

    Problem 2- I'm getting duplicates when I query the data and need some help in identifying why this is happening.

    Below is the XML:

    <?xml version="1.0" encoding="UTF-16"?>
    <PIES xmlns="http://www.aftermarket.org" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <Header>
    <PIESVersion>6.5</PIESVersion>
    <BlanketEffectiveDate>2018-11-06</BlanketEffectiveDate>
    <ParentAAIAID>CTLL</ParentAAIAID>
    <BrandOwnerAAIAID>DHQD</BrandOwnerAAIAID>
    </Header>
    <Items>
    <Item MaintenanceType="C">
    <BaseItemID>86-374GROEM</BaseItemID>
    <PartNumber>86-374GROEM</PartNumber>
    <BrandAAIAID>DHQD</BrandAAIAID>
    <BrandLabel>Recochem</BrandLabel>
    <PartTerminologyID>11393</PartTerminologyID>
    <Descriptions>
    <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">OEM Premium Extended Life Green Antifreeze / Coolant</Description>
    </Descriptions>
    <Packages>
    <Package MaintenanceType="C">
    <PackageUOM>EA</PackageUOM>
    <QuantityofEaches>1</QuantityofEaches>
    <Dimensions UOM="CM">
    <Height>28.7</Height>
    <Width>11.5</Width>
    <Length>18.8</Length>
    </Dimensions>
    <Weights UOM="KG">
    <Weight>4.58</Weight>
    </Weights>
    </Package>
    </Packages>
    <DigitalAssets>
    <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
    <FileName>86-374GROEM_GREEN_1.jpg</FileName>
    <AssetType>P04</AssetType>
    <FileType>JPG</FileType>
    <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-374GROEM_GREEN_1.jpg</URI&gt;
    <FileDateModified>2018-10-01</FileDateModified>
    <Country>US</Country>
    </DigitalFileInformation>
    </DigitalAssets>
    </Item>
    <Item MaintenanceType="C">
    <BaseItemID>86-384GROEMH</BaseItemID>
    <PartNumber>86-384GROEMH</PartNumber>
    <BrandAAIAID>DHQD</BrandAAIAID>
    <BrandLabel>Recochem</BrandLabel>
    <SubBrandAAIAID/>
    <PartTerminologyID>11393</PartTerminologyID>
    <DigitalAssets>
    <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
    <FileName>86-384GROEMH_GREEN_50-50_1.jpg</FileName>
    <AssetType>P04</AssetType>
    <FileType>JPG</FileType>
    <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-384GROEMH_GREEN_50-50_1.jpg</URI&gt;
    <FileDateModified>2018-10-01</FileDateModified>
    <Country>US</Country>
    </DigitalFileInformation>
    </DigitalAssets>
    </Item>
    </Items>
    <Trailer>
    <ItemCount>35</ItemCount>
    <TransactionDate>2018-11-06</TransactionDate>
    </Trailer>
    </PIES>

    Below is my query:
    SELECT distinct
    Header.DATA.value('(ParentAAIAID/text())[1]' ,'varchar(50)' ) AS ParentAAIAID,
    Item.DATA.value('(PartNumber/text())[1]' ,'varchar(50)' ) AS PartNumber,
    Descrip.DATA.value('(Description/text())[1]' ,'varchar(100)' ) AS Descrip,
    Img.DATA.value('(URI/text())[1]' ,'varchar(100)' ) AS URI

    FROM XML_TABLE TX
    CROSS APPLY TX.XML_DATA.nodes('*:PIES/*:Header') Header(DATA)
    CROSS APPLY TX.XML_DATA.nodes('*:PIES/*:Items/*:Item') Item(DATA)
    CROSS APPLY TX.XML_DATA.nodes('*:PIES/*:Items/*:Item/*:Descriptions') Descrip(DATA)
    CROSS APPLY TX.XML_DATA.nodes('*:PIES/*:Items/*:Item/*:DigitalAssets/*:DigitalFileInformation') Img(DATA)

    Below are the results I get AFTER I REMOVE: xmlns="http://www.aftermarket.org" xmlns:xs="http://www.w3.org/2001/XMLSchema" from the Pies tag.  Again I need leave this in and fix the wilcards above.
    Notice the Duplicate Values:

    Again, any help here would be much appreciatred.

    THanks again and Happy New Year!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    You are duplicating the results by applying the nodes method multiple times to the same elements.
    😎

    This is an example of the query without any duplication.

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @TXML XML = N'<?xml version="1.0" encoding="UTF-16"?>
    <PIES xmlns="http://www.aftermarket.org" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <Header>
      <PIESVersion>6.5</PIESVersion>
      <BlanketEffectiveDate>2018-11-06</BlanketEffectiveDate>
      <ParentAAIAID>CTLL</ParentAAIAID>
      <BrandOwnerAAIAID>DHQD</BrandOwnerAAIAID>
    </Header>
    <Items>
      <Item MaintenanceType="C">
      <BaseItemID>86-374GROEM</BaseItemID>
      <PartNumber>86-374GROEM</PartNumber>
      <BrandAAIAID>DHQD</BrandAAIAID>
      <BrandLabel>Recochem</BrandLabel>
      <PartTerminologyID>11393</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">OEM Premium Extended Life Green Antifreeze / Coolant</Description>
      </Descriptions>
      <Packages>
       <Package MaintenanceType="C">
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="CM">
        <Height>28.7</Height>
        <Width>11.5</Width>
        <Length>18.8</Length>
        </Dimensions>
        <Weights UOM="KG">
        <Weight>4.58</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>86-374GROEM_GREEN_1.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-374GROEM_GREEN_1.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
      <Item MaintenanceType="C">
      <BaseItemID>86-384GROEMH</BaseItemID>
      <PartNumber>86-384GROEMH</PartNumber>
      <BrandAAIAID>DHQD</BrandAAIAID>
      <BrandLabel>Recochem</BrandLabel>
      <SubBrandAAIAID />
      <PartTerminologyID>11393</PartTerminologyID>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>86-384GROEMH_GREEN_50-50_1.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-384GROEMH_GREEN_50-50_1.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
    </Items>
    <Trailer>
      <ItemCount>35</ItemCount>
      <TransactionDate>2018-11-06</TransactionDate>
    </Trailer>
    </PIES>';
    DECLARE @XML_TABLE TABLE (XML_DATA XML NOT NULL);
    INSERT INTO @XML_TABLE (XML_DATA) VALUES (@TXML);

    ;WITH XMLNAMESPACES (DEFAULT 'http://www.aftermarket.org')
    SELECT
    Header.DATA.value('(ParentAAIAID/text())[1]'                           ,'varchar(50)'  ) AS ParentAAIAID,
    Item.DATA.value('(PartNumber/text())[1]'                               ,'varchar(50)'  ) AS PartNumber,
    Item.DATA.value('(Descriptions/Description/text())[1]'                 ,'varchar(100)' ) AS Descrip,
    Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]' ,'varchar(100)' ) AS URI
    FROM @XML_TABLE TX
    CROSS APPLY TX.XML_DATA.nodes('PIES/Header')   Header(DATA)
    CROSS APPLY TX.XML_DATA.nodes('PIES/Items/Item') Item(DATA);

  • md44

    SSC Veteran

    Points: 210

    Eirikur Eiriksson - Wednesday, January 9, 2019 5:53 AM

    You are duplicating the results by applying the nodes method multiple times to the same elements.
    😎

    This is an example of the query without any duplication.

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @TXML XML = N'<?xml version="1.0" encoding="UTF-16"?>
    <PIES xmlns="http://www.aftermarket.org" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <Header>
      <PIESVersion>6.5</PIESVersion>
      <BlanketEffectiveDate>2018-11-06</BlanketEffectiveDate>
      <ParentAAIAID>CTLL</ParentAAIAID>
      <BrandOwnerAAIAID>DHQD</BrandOwnerAAIAID>
    </Header>
    <Items>
      <Item MaintenanceType="C">
      <BaseItemID>86-374GROEM</BaseItemID>
      <PartNumber>86-374GROEM</PartNumber>
      <BrandAAIAID>DHQD</BrandAAIAID>
      <BrandLabel>Recochem</BrandLabel>
      <PartTerminologyID>11393</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">OEM Premium Extended Life Green Antifreeze / Coolant</Description>
      </Descriptions>
      <Packages>
       <Package MaintenanceType="C">
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="CM">
        <Height>28.7</Height>
        <Width>11.5</Width>
        <Length>18.8</Length>
        </Dimensions>
        <Weights UOM="KG">
        <Weight>4.58</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>86-374GROEM_GREEN_1.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-374GROEM_GREEN_1.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
      <Item MaintenanceType="C">
      <BaseItemID>86-384GROEMH</BaseItemID>
      <PartNumber>86-384GROEMH</PartNumber>
      <BrandAAIAID>DHQD</BrandAAIAID>
      <BrandLabel>Recochem</BrandLabel>
      <SubBrandAAIAID />
      <PartTerminologyID>11393</PartTerminologyID>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>86-384GROEMH_GREEN_50-50_1.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/71E/86-384GROEMH_GREEN_50-50_1.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
    </Items>
    <Trailer>
      <ItemCount>35</ItemCount>
      <TransactionDate>2018-11-06</TransactionDate>
    </Trailer>
    </PIES>';
    DECLARE @XML_TABLE TABLE (XML_DATA XML NOT NULL);
    INSERT INTO @XML_TABLE (XML_DATA) VALUES (@TXML);

    ;WITH XMLNAMESPACES (DEFAULT 'http://www.aftermarket.org')
    SELECT
    Header.DATA.value('(ParentAAIAID/text())[1]'                           ,'varchar(50)'  ) AS ParentAAIAID,
    Item.DATA.value('(PartNumber/text())[1]'                               ,'varchar(50)'  ) AS PartNumber,
    Item.DATA.value('(Descriptions/Description/text())[1]'                 ,'varchar(100)' ) AS Descrip,
    Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]' ,'varchar(100)' ) AS URI
    FROM @XML_TABLE TX
    CROSS APPLY TX.XML_DATA.nodes('PIES/Header')   Header(DATA)
    CROSS APPLY TX.XML_DATA.nodes('PIES/Items/Item') Item(DATA);

    You make it look so easy 🙂  Thank you so much.  Truly appreciate the help!

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply