Problem reading all Attributes into SQL table

  • md44

    SSC Veteran

    Points: 210

    md44 - Wednesday, January 9, 2019 2:17 PM

    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!

    One quick question.  Some of the elements contain multiple descriptions an images (example below). 
    <?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>
    <SubmissionType>FULL</SubmissionType>
    <BlanketEffectiveDate>2018-11-07</BlanketEffectiveDate>
    <ParentAAIAID>CSNQ</ParentAAIAID>
    <BrandOwnerAAIAID>BGND</BrandOwnerAAIAID>
    <CurrencyCode>USD</CurrencyCode>
    <LanguageCode>EN</LanguageCode>
    </Header>
    <Items>
    <Item MaintenanceType="C">
    <HazardousMaterialCode>N</HazardousMaterialCode>
    <PartNumber>138750</PartNumber>
    <BrandAAIAID>BGND</BrandAAIAID>
    <BrandLabel>Superchips</BrandLabel>
    <SubBrandAAIAID/>
    <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
    <PartTerminologyID>12027</PartTerminologyID>
    <Descriptions>
    <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">VIVID LINQ Programmer</Description>
    <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
    </Descriptions>
    <ExtendedInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
    </ExtendedInformation>
    <Packages>
    <Package MaintenanceType="C">
    <PackageLevelGTIN>853118003100</PackageLevelGTIN>
    <PackageUOM>EA</PackageUOM>
    <QuantityofEaches>1</QuantityofEaches>
    <Dimensions UOM="IN">
    <Height>2.75</Height>
    <Width>6.25</Width>
    <Length>9.5</Length>
    </Dimensions>
    <Weights UOM="PG">
    <Weight>2</Weight>
    </Weights>
    </Package>
    </Packages>
    <DigitalAssets>
    <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
    <FileName>0000368_300.jpg</FileName>
    <AssetType>P04</AssetType>
    <FileType>JPG</FileType>
    <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000368_300.jpg</URI&gt;
    <FileDateModified>2018-10-01</FileDateModified>
    <Country>US</Country>
    </DigitalFileInformation>
    </DigitalAssets>
    </Item>
    <Item MaintenanceType="C">
    <HazardousMaterialCode>N</HazardousMaterialCode>
    <PartNumber>3855</PartNumber>
    <BrandAAIAID>BGND</BrandAAIAID>
    <BrandLabel>Superchips</BrandLabel>
    <SubBrandAAIAID/>
    <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
    <PartTerminologyID>12027</PartTerminologyID>
    <Descriptions>
    <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">Flashpaq Programmer</Description>
    <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
    </Descriptions>
    <ExtendedInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
    <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
    </ExtendedInformation>
    <Packages>
    <Package MaintenanceType="C">
    <PackageLevelGTIN>894520001681</PackageLevelGTIN>
    <PackageUOM>EA</PackageUOM>
    <QuantityofEaches>1</QuantityofEaches>
    <Dimensions UOM="IN">
    <Height>2.5</Height>
    <Width>6</Width>
    <Length>5</Length>
    </Dimensions>
    <Weights UOM="PG">
    <Weight>0.8</Weight>
    </Weights>
    </Package>
    </Packages>
    <DigitalAssets>
    <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
    <FileName>0000200_300.jpg</FileName>
    <AssetType>P04</AssetType>
    <FileType>JPG</FileType>
    <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000200_300.jpg</URI&gt;
    <FileDateModified>2018-10-01</FileDateModified>
    <Country>US</Country>
    </DigitalFileInformation>
    </DigitalAssets>
    </Item>
    </Items>
    </PIES>

    This query does not show the multiple descriptions - it only shows the first.  I assume because I am asking for a singleton using [1].  Can this be modified to return ALL description and ALL images?  I'm ok if the other records are duplicated 
    �.  Query Below:

    ;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('(BrandLabel/text())[1]'           ,'varchar(50)' ) AS BrandLabel,
    Item.DATA.value('(Descriptions/Description/text())[1]'      ,'varchar(100)' ) AS Descrip,
    Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'      ,'varchar(100)' ) AS Length,
    Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'      ,'varchar(100)' ) AS Width,
    Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'      ,'varchar(100)' ) AS Height,
    Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'      ,'varchar(100)' ) AS Weight,
    Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]' ,'varchar(100)' ) AS Image1,
    Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    Few options, here are three of them.
    😎


    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- https://www.sqlservercentral.com/Forums/2013480/Problem-reading-all-Attributes-into-SQL-table
    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>
      <SubmissionType>FULL</SubmissionType>
      <BlanketEffectiveDate>2018-11-07</BlanketEffectiveDate>
      <ParentAAIAID>CSNQ</ParentAAIAID>
      <BrandOwnerAAIAID>BGND</BrandOwnerAAIAID>
      <CurrencyCode>USD</CurrencyCode>
      <LanguageCode>EN</LanguageCode>
    </Header>
    <Items>
      <Item MaintenanceType="C">
      <HazardousMaterialCode>N</HazardousMaterialCode>
      <PartNumber>138750</PartNumber>
      <BrandAAIAID>BGND</BrandAAIAID>
      <BrandLabel>Superchips</BrandLabel>
      <SubBrandAAIAID />
      <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
      <PartTerminologyID>12027</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">VIVID LINQ Programmer</Description>
       <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
      </Descriptions>
      <ExtendedInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
      </ExtendedInformation>
      <Packages>
       <Package MaintenanceType="C">
        <PackageLevelGTIN>853118003100</PackageLevelGTIN>
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="IN">
        <Height>2.75</Height>
        <Width>6.25</Width>
        <Length>9.5</Length>
        </Dimensions>
        <Weights UOM="PG">
        <Weight>2</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>0000368_300.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000368_300.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
      <Item MaintenanceType="C">
      <HazardousMaterialCode>N</HazardousMaterialCode>
      <PartNumber>3855</PartNumber>
      <BrandAAIAID>BGND</BrandAAIAID>
      <BrandLabel>Superchips</BrandLabel>
      <SubBrandAAIAID />
      <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
      <PartTerminologyID>12027</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">Flashpaq Programmer</Description>
       <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
      </Descriptions>
      <ExtendedInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
      </ExtendedInformation>
      <Packages>
       <Package MaintenanceType="C">
        <PackageLevelGTIN>894520001681</PackageLevelGTIN>
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="IN">
        <Height>2.5</Height>
        <Width>6</Width>
        <Length>5</Length>
        </Dimensions>
        <Weights UOM="PG">
        <Weight>0.8</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>0000200_300.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000200_300.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
    </Items>
    </PIES>';
    DECLARE @XML_TABLE TABLE (XML_DATA XML NOT NULL);
    INSERT INTO @XML_TABLE (XML_DATA) VALUES (@TXML);

    -- Option 1, fixed number of multiple-items
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Item.DATA.value('(Descriptions/Description/text())[1]'                       ,'varchar(100)' ) AS Descrip1
     ,Item.DATA.value('(Descriptions/Description/text())[2]'                       ,'varchar(100)' ) AS Descrip2
     ,Item.DATA.value('(Descriptions/Description/text())[3]'                       ,'varchar(100)' ) AS Descrip3
     ,Item.DATA.value('(Descriptions/Description/text())[4]'                       ,'varchar(100)' ) AS Descrip4
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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);

    -- Option 2, unlimited number of items
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Descr.DATA.value('(./text())[1]'                                             ,'varchar(100)' ) AS Descrip1
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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 Item.DATA.nodes('Descriptions/Description')  Descr(DATA);

    -- Option 3, multiple items as single XML blob
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Item.DATA.query('Descriptions/*'                                                             ) AS DescripXML
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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 - Thursday, January 10, 2019 12:51 AM

    Few options, here are three of them.
    😎


    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- https://www.sqlservercentral.com/Forums/2013480/Problem-reading-all-Attributes-into-SQL-table
    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>
      <SubmissionType>FULL</SubmissionType>
      <BlanketEffectiveDate>2018-11-07</BlanketEffectiveDate>
      <ParentAAIAID>CSNQ</ParentAAIAID>
      <BrandOwnerAAIAID>BGND</BrandOwnerAAIAID>
      <CurrencyCode>USD</CurrencyCode>
      <LanguageCode>EN</LanguageCode>
    </Header>
    <Items>
      <Item MaintenanceType="C">
      <HazardousMaterialCode>N</HazardousMaterialCode>
      <PartNumber>138750</PartNumber>
      <BrandAAIAID>BGND</BrandAAIAID>
      <BrandLabel>Superchips</BrandLabel>
      <SubBrandAAIAID />
      <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
      <PartTerminologyID>12027</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">VIVID LINQ Programmer</Description>
       <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
      </Descriptions>
      <ExtendedInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
      </ExtendedInformation>
      <Packages>
       <Package MaintenanceType="C">
        <PackageLevelGTIN>853118003100</PackageLevelGTIN>
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="IN">
        <Height>2.75</Height>
        <Width>6.25</Width>
        <Length>9.5</Length>
        </Dimensions>
        <Weights UOM="PG">
        <Weight>2</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>0000368_300.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000368_300.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
      <Item MaintenanceType="C">
      <HazardousMaterialCode>N</HazardousMaterialCode>
      <PartNumber>3855</PartNumber>
      <BrandAAIAID>BGND</BrandAAIAID>
      <BrandLabel>Superchips</BrandLabel>
      <SubBrandAAIAID />
      <MinimumOrderQuantity UOM="EA">1</MinimumOrderQuantity>
      <PartTerminologyID>12027</PartTerminologyID>
      <Descriptions>
       <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN">Flashpaq Programmer</Description>
       <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN">CpChipProgmer</Description>
      </Descriptions>
      <ExtendedInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="CTO" LanguageCode="EN">US</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="LIF" LanguageCode="EN">4</ExtendedProductInformation>
       <ExtendedProductInformation MaintenanceType="C" EXPICode="NAF" LanguageCode="EN">B</ExtendedProductInformation>
      </ExtendedInformation>
      <Packages>
       <Package MaintenanceType="C">
        <PackageLevelGTIN>894520001681</PackageLevelGTIN>
        <PackageUOM>EA</PackageUOM>
        <QuantityofEaches>1</QuantityofEaches>
        <Dimensions UOM="IN">
        <Height>2.5</Height>
        <Width>6</Width>
        <Length>5</Length>
        </Dimensions>
        <Weights UOM="PG">
        <Weight>0.8</Weight>
        </Weights>
       </Package>
      </Packages>
      <DigitalAssets>
       <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
        <FileName>0000200_300.jpg</FileName>
        <AssetType>P04</AssetType>
        <FileType>JPG</FileType>
        <URI>https://images.wrenchead.com/smartpages/partinfo_resize/SCS/0000200_300.jpg</URI&gt;
        <FileDateModified>2018-10-01</FileDateModified>
        <Country>US</Country>
       </DigitalFileInformation>
      </DigitalAssets>
      </Item>
    </Items>
    </PIES>';
    DECLARE @XML_TABLE TABLE (XML_DATA XML NOT NULL);
    INSERT INTO @XML_TABLE (XML_DATA) VALUES (@TXML);

    -- Option 1, fixed number of multiple-items
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Item.DATA.value('(Descriptions/Description/text())[1]'                       ,'varchar(100)' ) AS Descrip1
     ,Item.DATA.value('(Descriptions/Description/text())[2]'                       ,'varchar(100)' ) AS Descrip2
     ,Item.DATA.value('(Descriptions/Description/text())[3]'                       ,'varchar(100)' ) AS Descrip3
     ,Item.DATA.value('(Descriptions/Description/text())[4]'                       ,'varchar(100)' ) AS Descrip4
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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);

    -- Option 2, unlimited number of items
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Descr.DATA.value('(./text())[1]'                                             ,'varchar(100)' ) AS Descrip1
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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 Item.DATA.nodes('Descriptions/Description')  Descr(DATA);

    -- Option 3, multiple items as single XML blob
    ;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('(BrandLabel/text())[1]'                                     ,'varchar(50)'  ) AS BrandLabel
     ,Item.DATA.query('Descriptions/*'                                                             ) AS DescripXML
     ,Item.DATA.value('(Packages/Package/Dimensions/Length/text())[1]'             ,'varchar(100)' ) AS Length
     ,Item.DATA.value('(Packages/Package/Dimensions/Width/text())[1]'              ,'varchar(100)' ) AS Width
     ,Item.DATA.value('(Packages/Package/Dimensions/Height/text())[1]'             ,'varchar(100)' ) AS Height
     ,Item.DATA.value('(Packages/Package/Weights/Weight/text())[1]'                ,'varchar(100)' ) AS Weight
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/URI/text())[1]'       ,'varchar(100)' ) AS Image1
     ,Item.DATA.value('(DigitalAssets/DigitalFileInformation/AssetType/text())[1]' ,'varchar(100)' ) AS AssetType
    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);

    This works.  Again, thank you so much!.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    You are very welcome.
    😎

  • mfconners

    SSC Veteran

    Points: 267

    Replace:
    FROM @TXML.nodes('ACES/App') APP(DATA);

    With something like this:
    FROM dbo.XML_Staging AS stg
    CROSS APPLY stg.TXML.nodes('ACES/App') APP(DATA);

  • mfconners

    SSC Veteran

    Points: 267

    mfconners - Thursday, January 10, 2019 5:31 AM

    Replace:
    FROM @TXML.nodes('ACES/App') APP(DATA);

    With something like this:
    FROM dbo.XML_Staging AS stg
    CROSS APPLY stg.TXML.nodes('ACES/App') APP(DATA);

    I was posting from and I didn't see the additional pages of response.  Please ignore me.  😐

Viewing 6 posts - 31 through 36 (of 36 total)

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