CODEWITHSUNDEEP
sql-serverxmlt-sqlxquerysql-server-2014
PSA
PSA

Reputation: 1

XML Multiple nodes with same name but different attributes SQL

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'

Upvotes: 0

Views: 263

Answers (1)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

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.

Upvotes: 2

Related Questions