Problem reading all Attributes into SQL table

Question

Problem reading all Attributes into SQL table

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

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

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 & 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 & 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: 260 More actions · Answer 2

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: 182916 More actions · Answer 3

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: 260 More actions · Answer 4

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>

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: 76997 More actions · Answer 5

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: 260 More actions · Answer 6

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: 442467 More actions · Answer 7

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: 442467 More actions · Answer 8

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

md44 SSC Veteran Points: 260 More actions · Answer 9

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: 76997 More actions · Answer 10

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: 260 More actions · Answer 11

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: 76997 More actions · Answer 12

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: 260 More actions · Answer 13

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

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: 182916 More actions · Answer 14

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

md44 SSC Veteran Points: 260 More actions · Answer 15

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!