Problem reading all Attributes into SQL table

• md44

  SSC Veteran

  Points: 260

  More actions

  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&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: 182882

  More actions

  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&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: 260

  More actions

  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&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: 182882

  More actions

  January 10, 2019 at 3:21 am

  #2018497

  You are very welcome.
  😎

• mfconners

  Say Hey Kid

  Points: 707

  More actions

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

• mfconners

  Say Hey Kid

  Points: 707

  More actions

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