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...
May 28, 2024 at 5:05 pm
If you want a coded solution, please provide a (sanitised as needed) version of the XML doc, not an image!
May 28, 2024 at 7:53 pm
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)
May 29, 2024 at 9:23 am
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);
May 29, 2024 at 1:05 pm
thx phil, i'm going to try to build on your structure to get those features. I'll post results back here.
May 29, 2024 at 1:50 pm
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).
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)
May 29, 2024 at 3:05 pm
Deleted duplicate
May 29, 2024 at 3:06 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy