Post reply

Problem reading all Attributes into SQL table

  • January 9, 2019 at 5:46 pm

    #2018473

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

  • January 10, 2019 at 12:51 am

    #2018481

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

  • January 10, 2019 at 3:07 am

    #2018496

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

  • January 10, 2019 at 3:21 am

    #2018497

    You are very welcome.
    😎

  • January 10, 2019 at 5:31 am

    #2018509

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

  • January 10, 2019 at 5:37 am

    #2018511

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy