singleton is interferring with getting multiple nodes in xml

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

    xmlissue2

     

     

    xmlissues

     

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

    • This reply was modified 3 weeks, 5 days ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 3 weeks, 5 days ago by  stan. Reason: clarity
  • 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);

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

  • 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

     

  • 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 3 weeks, 4 days ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Deleted duplicate

    • This reply was modified 3 weeks, 3 days ago by  Jonathan AC Roberts. Reason: Deleted duplicate
  • 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)

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

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