XML Multiple nodes with same name but different attributes SQL

Question

I would like to return the Dimensions where the PackageUOM is 'EA'. Currently, I am only getting the first subnode, this is causing problems!

I know that I would only like to pull the dimensions if the PackageUOM is EA, I am not sure how to do this. Any help would be appreciated. Thank you.

for reference this is in SQL 2014

<Packages>
        <Package MaintenanceType="A">
          <PackageUOM>PL</PackageUOM>
          <QuantityofEaches>50</QuantityofEaches>
          <Orderable>Y</Orderable>
          <Dimensions UOM="IN">
            <Height>40.7500</Height>
            <Width>42.0000</Width>
            <Length>48.0000</Length>
          </Dimensions>
        </Package>
        <Package MaintenanceType="A">
          <PackageUOM>EA</PackageUOM>
          <QuantityofEaches>1</QuantityofEaches>
          <Orderable>Y</Orderable>
          <Dimensions UOM="IN">
            <Height>7.2500</Height>
            <Width>17.0000</Width>
            <Length>10.2500</Length>
          </Dimensions>
          <Weights UOM="PG">
            <Weight>8.8400</Weight>
          </Weights>
        </Package>
      </Packages>


SELECT *
    FROM OPENXML(@handle, 'a:PIES/a:Items/a:Item',2)
    WITH (
        [TestFile] varchar(max) '../../a:TestFile',
        [PIESVersion] varchar(max) '../../a:Header/a:PIESVersion',
        [SubmissionType] varchar(max) '../../a:Header/a:SubmissionType',
        [LanguageCode] varchar(max) '../../a:Header/a:LanguageCode',
        [TechnicalContact] varchar(max) '../../a:Header/a:TechnicalContact',
        [ContactEmail] varchar(max) '../../a:Header/a:ContactEmail',
        [PCdbVersionDate] varchar(max) '../../a:Header/a:PCdbVersionDate',
        [PAdbVersionDate] varchar(max) '../../a:Header/a:PAdbVersionDate',
        [ItemLevelGTIN] varchar(max) 'a:ItemLevelGTIN',
        [PartNumber] varchar(max) 'a:PartNumber',
        [BrandAAIAID] varchar(max) 'a:BrandAAIAID',
        [BrandLabel] varchar(max) 'a:BrandLabel',
        [SubBrandAAIAID] varchar(max) 'a:SubBrandAAIAID',
        [SubBrandLabel] varchar(max) 'a:SubBrandLabel',
        [PartTerminologyID] varchar(max) 'a:PartTerminologyID',
        [PackageUOM] varchar(max) 'a:Packages/a:Package/a:PackageUOM',
        [PackageQuantityofEaches] varchar(max) 'a:Packages/a:Package/a:QuantityofEaches',
        [PackageDimensionUOM] varchar(10) 'a:Packages/a:Package/a:Dimensions/@UOM',
        [PackageHeight] decimal(9,4) 'a:Packages/a:Package/a:Dimensions/a:Height',
        [PackageWidth] decimal(9,4) 'a:Packages/a:Package/a:Dimensions/a:Width',
        [PackageLength] decimal(9,4) 'a:Packages/a:Package/a:Dimensions/a:Length',
        [PackageWeightUOM] varchar(max) 'a:Packages/a:Package/a:Weights/@UOM',
        [PackageWeight] decimal(9,4) 'a:Packages/a:Package/a:Weights/a:Weight',
        [PackageUPC] varchar(max) 'a:Packages/a:Package/a:PackageLevelGTIN'     
    ) A
    WHERE A.[PartNumber]='OW7168'

PSA, While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic, and your code attempt implementation of it. (3) Desired output based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 4, 2020 at 1:36
PSA, Please provide what I mentioned earlier. Your SELECT statement is not matching XML structure. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 4, 2020 at 13:10

Srinika Pinnaduwage · Accepted Answer · 2020-06-04 00:39:43Z

2

Are you looking for something like the following?

Declare @x xml = '<Packages>
        <Package MaintenanceType="A">
          <PackageUOM>PL</PackageUOM>
          <QuantityofEaches>50</QuantityofEaches>
          <Orderable>Y</Orderable>
          <Dimensions UOM="IN">
            <Height>40.7500</Height>
            <Width>42.0000</Width>
            <Length>48.0000</Length>
          </Dimensions>
        </Package>
        <Package MaintenanceType="A">
          <PackageUOM>EA</PackageUOM>
          <QuantityofEaches>1</QuantityofEaches>
          <Orderable>Y</Orderable>
          <Dimensions UOM="IN">
            <Height>7.2500</Height>
            <Width>17.0000</Width>
            <Length>10.2500</Length>
          </Dimensions>
          <Weights UOM="PG">
            <Weight>8.8400</Weight>
          </Weights>
        </Package>
      </Packages>'

select node.value('(Dimensions/Height/text())[1]', 'VARCHAR(20)') AS [Height],
       node.value('(Dimensions/Width/text())[1]', 'VARCHAR(20)') AS [Width],
       node.value('(Dimensions/Length/text())[1]', 'VARCHAR(20)') AS [Length]
from @x.nodes('/Packages/Package[PackageUOM/text() = "EA"]') AS nodes(node);

In this you get the data filtered for PackageUOM = 'EA' and get the dimensions for that record as 3 columns

Also note that I have put 'VARCHAR(20)' instead of 'DECIMAL(18,4)', to avoid crashing due any non numeric data as units of measurement as meters or so.

answered Jun 4, 2020 at 0:39

Srinika Pinnaduwage

1,0421 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PSA Over a year ago

Is there a way to do this using OpenXML?

Srinika Pinnaduwage Over a year ago

@PSA, OpenXML is the older way, and it is lengthy as well. If this helped you, please mark this as answer.

Collectives™ on Stack Overflow

XML Multiple nodes with same name but different attributes SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related