singleton is interferring with getting multiple nodes in xml

Question

Post reply

singleton is interferring with getting multiple nodes in xml

stan

Ten Centuries

Points: 1000
More actions
May 28, 2024 at 3:09 pm
Go to Answer
#4424453

Hi we have an xml doc shaped essentially as shown in image 2 below. Unfortunately we are so used to using the singleton structure to avoid problems that we dont know how to get multiple nodes like those inside featuresandoptions . At the moment we are only getting one occurrence of each for each line number using the following code...there is only 1 xxxxindex in our payload, multiple linenumbers in our payload and as shown multiple productfeatures under one featuresandoptions under each line number...

Phil Parkin

SSC Guru

Points: 246985
More actions
May 29, 2024 at 2:48 pm
Answer
#4424681
Try this instead
```
    CROSS APPLY sx.XMLDoc.nodes ('root/Header') C(x)
    CROSS APPLY sx.XMLDoc.nodes ('root/PurchaseOrder') d(y)
    CROSS APPLY y.nodes ('PurchaseOrderLine') e(z)
CROSS APPLY z.nodes ('FeaturesAndOptions/ProductFeatures') f(a)
```
- This reply was modified 6 months, 3 weeks ago by Phil Parkin.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

If you want a coded solution, please provide a (sanitised as needed) version of the XML doc, not an image!

This reply was modified 6 months, 3 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

stan Ten Centuries Points: 1000 More actions · Answer 2

sure phil, this payload and query are representative. we only get one set of features for each line number.

declare @xml xml=
'<root>
  <Header>
    <xxxxxName>yyyyy</xxxxxName>
    <FileFormat>zzzzz</FileFormat>
    <FileName>aaaaa</FileName>
  </Header>
  <PurchaseOrder>
    <abcdefIndex>1</abcdefIndex>
    <PurchaseOrderNumber>ggggg</PurchaseOrderNumber>
    <SupplierID>cdcdcdcd</SupplierID>
    <SupplierName>abababab</SupplierName>
    <Shipto>
      <AttentionOfName>efefefef</AttentionOfName>
      <StreetName>Main</StreetName>
      <CityName>some city</CityName>
      <CountrySubDivisionCode>SS</CountrySubDivisionCode>
      <CountryCode>US</CountryCode>
      <PostalCode>ghghg</PostalCode>
    </Shipto>
    <PaymentTerm>x</PaymentTerm>
    <PurchaseOrderLine>
      <abcdefIndex>2</abcdefIndex>
      <LineNumber>1</LineNumber>
      <Item>yyyyyyyyyy</Item>
      <Quantity unitCode="EA">1</Quantity>
      <UnitPrice currencyID="USD">0.0</UnitPrice>
      <Amount currencyID="USD">0.0</Amount>
      <RequiredDeliveryDateTime>2024-05-30T20:00:00Z</RequiredDeliveryDateTime>
      <ShippingWarehouse>ijijij</ShippingWarehouse>
      <ShippingWarehouseName>klklkl</ShippingWarehouseName>
      <AssemblySequence>mnmnmn</AssemblySequence>
      <Machine>opopop</Machine>
      <FeaturesAndOptions>
        <ProductFeatures>
          <Feature>qrqr</Feature>
          <FeatureDesc>qrqrqr</FeatureDesc>
          <OptionValue>qrqr</OptionValue>
          <OptionDesc>ststst</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>Iststst</Feature>
          <FeatureDesc>ststst</FeatureDesc>
          <OptionValue>sts</OptionValue>
          <OptionDesc>ststst</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>uvuvuv</Feature>
          <FeatureDesc>uv</FeatureDesc>
          <OptionValue>uv</OptionValue>
          <OptionDesc>uv</OptionDesc>
        </ProductFeatures> 
      </FeaturesAndOptions>
    </PurchaseOrderLine>
    <PurchaseOrderLine>
      <abcdefIndex>3</abcdefIndex>
      <LineNumber>2</LineNumber>
      <Item>whatever</Item>
      <Quantity unitCode="EA">1</Quantity>
      <UnitPrice currencyID="USD">0.0</UnitPrice>
      <Amount currencyID="USD">0.0</Amount>
      <RequiredDeliveryDateTime>2024-05-30T20:00:00Z</RequiredDeliveryDateTime>
      <ShippingWarehouse>whate</ShippingWarehouse>
      <ShippingWarehouseName>whate</ShippingWarehouseName>
      <AssemblySequence>whatever1</AssemblySequence>
      <Machine>what</Machine>
      <FeaturesAndOptions>
        <ProductFeatures>
          <Feature>whatever</Feature>
          <FeatureDesc>whatever</FeatureDesc>
          <OptionValue>whatever</OptionValue>
          <OptionDesc>whatever</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>whatever1</Feature>
          <FeatureDesc>whatever1</FeatureDesc>
          <OptionValue>whatever1</OptionValue>
          <OptionDesc>whatever1</OptionDesc>
        </ProductFeatures>
       
      </FeaturesAndOptions>
    </PurchaseOrderLine>
  </PurchaseOrder>
</root>'

declare @uuuImportID int=1
      select @uuuImportID,

 1 hdrid,


             line.value('abcdefIndex[1]', 'int') [Index],
             line.value('LineNumber[1]', 'nvarchar(10)') LineNumber,
             line.value('(FeaturesAndOptions/ProductFeatures/Feature)[1]', 'nvarchar(100)') Feature,
             line.value('(FeaturesAndOptions/ProductFeatures/FeatureDesc)[1]', 'nvarchar(500)') FeatureDesc,
             line.value('(FeaturesAndOptions/ProductFeatures/OptionValue)[1]', 'nvarchar(100)') OptionValue,
             line.value('(FeaturesAndOptions/ProductFeatures/OptionDesc)[1]', 'nvarchar(500)') OptionDesc
        from @xml.nodes('/root/PurchaseOrder/PurchaseOrderLine') as li(line),
             @xml.nodes('/root/PurchaseOrder') as mynode(po)

This reply was modified 6 months, 3 weeks ago by stan. Reason: clarity

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

See whether this helps.

DROP TABLE IF EXISTS #SomeXML;

CREATE TABLE #SomeXML
(
    XMLDoc XML
);

INSERT #SomeXML
(
    XMLDoc
)
VALUES
('<root>
  <Header>
    <xxxxxName>yyyyy</xxxxxName>
    <FileFormat>zzzzz</FileFormat>
    <FileName>aaaaa</FileName>
  </Header>
  <PurchaseOrder>
    <abcdefIndex>1</abcdefIndex>
    <PurchaseOrderNumber>ggggg</PurchaseOrderNumber>
    <SupplierID>cdcdcdcd</SupplierID>
    <SupplierName>abababab</SupplierName>
    <Shipto>
      <AttentionOfName>efefefef</AttentionOfName>
      <StreetName>Main</StreetName>
      <CityName>some city</CityName>
      <CountrySubDivisionCode>SS</CountrySubDivisionCode>
      <CountryCode>US</CountryCode>
      <PostalCode>ghghg</PostalCode>
    </Shipto>
    <PaymentTerm>x</PaymentTerm>
    <PurchaseOrderLine>
      <abcdefIndex>2</abcdefIndex>
      <LineNumber>1</LineNumber>
      <Item>yyyyyyyyyy</Item>
      <Quantity unitCode="EA">1</Quantity>
      <UnitPrice currencyID="USD">0.0</UnitPrice>
      <Amount currencyID="USD">0.0</Amount>
      <RequiredDeliveryDateTime>2024-05-30T20:00:00Z</RequiredDeliveryDateTime>
      <ShippingWarehouse>ijijij</ShippingWarehouse>
      <ShippingWarehouseName>klklkl</ShippingWarehouseName>
      <AssemblySequence>mnmnmn</AssemblySequence>
      <Machine>opopop</Machine>
      <FeaturesAndOptions>
        <ProductFeatures>
          <Feature>qrqr</Feature>
          <FeatureDesc>qrqrqr</FeatureDesc>
          <OptionValue>qrqr</OptionValue>
          <OptionDesc>ststst</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>Iststst</Feature>
          <FeatureDesc>ststst</FeatureDesc>
          <OptionValue>sts</OptionValue>
          <OptionDesc>ststst</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>uvuvuv</Feature>
          <FeatureDesc>uv</FeatureDesc>
          <OptionValue>uv</OptionValue>
          <OptionDesc>uv</OptionDesc>
        </ProductFeatures> 
      </FeaturesAndOptions>
    </PurchaseOrderLine>
    <PurchaseOrderLine>
      <abcdefIndex>3</abcdefIndex>
      <LineNumber>2</LineNumber>
      <Item>whatever</Item>
      <Quantity unitCode="EA">1</Quantity>
      <UnitPrice currencyID="USD">0.0</UnitPrice>
      <Amount currencyID="USD">0.0</Amount>
      <RequiredDeliveryDateTime>2024-05-30T20:00:00Z</RequiredDeliveryDateTime>
      <ShippingWarehouse>whate</ShippingWarehouse>
      <ShippingWarehouseName>whate</ShippingWarehouseName>
      <AssemblySequence>whatever1</AssemblySequence>
      <Machine>what</Machine>
      <FeaturesAndOptions>
        <ProductFeatures>
          <Feature>whatever</Feature>
          <FeatureDesc>whatever</FeatureDesc>
          <OptionValue>whatever</OptionValue>
          <OptionDesc>whatever</OptionDesc>
        </ProductFeatures>
        <ProductFeatures>
          <Feature>whatever1</Feature>
          <FeatureDesc>whatever1</FeatureDesc>
          <OptionValue>whatever1</OptionValue>
          <OptionDesc>whatever1</OptionDesc>
        </ProductFeatures>
       
      </FeaturesAndOptions>
    </PurchaseOrderLine>
  </PurchaseOrder>
</root>');

SELECT fileformat = x.value ('FileFormat[1]', 'varchar(50)')
      ,PurchaseOrderNumber = y.value ('PurchaseOrderNumber[1]', 'varchar(50)')
      ,PurchaseOrderLineNumber = z.value ('LineNumber[1]', 'varchar(50)')
      ,PurchaseOrderItem = z.value ('Item[1]', 'varchar(50)')
FROM #SomeXML sx
    CROSS APPLY sx.XMLDoc.nodes ('//root/Header') C(x)
    CROSS APPLY sx.XMLDoc.nodes ('//root/PurchaseOrder') d(y)
    CROSS APPLY y.nodes ('//PurchaseOrderLine') e(z);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

stan Ten Centuries Points: 1000 More actions · Answer 4

thx phil, i'm going to try to build on your structure to get those features. I'll post results back here.

stan Ten Centuries Points: 1000 More actions · Answer 5

we are still having a problem down on the features where the singleton was originally interfering. All features from all lines are showing up on all lines. Below is an image of one thing i tried but i tried a number of other things too including

CROSS APPLY sx.XMLDoc.nodes ('//root/PurchaseOrder/PurchaseOrderLine/FeaturesAndOptions/ProductFeatures') f(a).

xml3

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 6

Deleted duplicate

This reply was modified 6 months, 3 weeks ago by Jonathan AC Roberts. Reason: Deleted duplicate

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 7

select @uuuImportID,
       1 as hdrid,
       line.value('(abcdefIndex)[1]', 'int') as [Index],
       line.value('(LineNumber)[1]', 'nvarchar(10)') as LineNumber,
       features.value('(Feature)[1]', 'nvarchar(100)') as Feature,
       features.value('(FeatureDesc)[1]', 'nvarchar(500)') as FeatureDesc,
       features.value('(OptionValue)[1]', 'nvarchar(100)') as OptionValue,
       features.value('(OptionDesc)[1]', 'nvarchar(500)') as OptionDesc
  from @xml.nodes('/root/PurchaseOrder/PurchaseOrderLine') as li(line)
 cross apply line.nodes('FeaturesAndOptions/ProductFeatures') as pf(features)