abrendalson
New Member
I am experiencing an issue when I try to form an XML file using FOR XML. I am using the following query:\[code\] (select (select ROW_NUMBER() over (PARTITION BY q7.nr_factura order by q7.itemkey) as 'Line-Number', cod_ean as 'EAN', cod_ka as 'BuyerItemCode', itemkey as 'SupplierItemCode', itemname as 'ItemDescription', itemtype as 'ItemType', quantity as 'InvoiceQuantity', price as 'InvoiceUnitNetPrice', cast(tva as int) as 'TaxRate', taxcategorycode as 'TaxCategoryCode', val_tva_produs as 'TaxAmount', val_neta_produs as 'NetAmount', val_bruta_rotunjita as 'GrossAmount' from EDI.dbo.EDI_INVOICE_GEN q7 where q7.nr_factura=q.nr_factura for XML PATH('Line-Item'),type) from EDI.dbo.EDI_INVOICE_GEN q9 where q9.nr_factura=q.nr_factura group by nr_factura for xml path(''),root ('Invoice-Lines'),TYPE)\[/code\]The result is this: \[code\] <Invoice-Lines><Line-Item> <Line-Number>1</Line-Number> <EAN>42139430</EAN> <BuyerItemCode>16500006</BuyerItemCode> <SupplierItemCode>TCABKL03</SupplierItemCode> <ItemDescription>CAMEL BLACK PMx13.3</ItemDescription> <ItemType>CU</ItemType> <InvoiceQuantity>1</InvoiceQuantity> <InvoiceUnitNetPrice>100.25</InvoiceUnitNetPrice> <TaxRate>24</TaxRate> <TaxCategoryCode>S</TaxCategoryCode> <TaxAmount>19.4</TaxAmount> <NetAmount>80.85</NetAmount> <GrossAmount>100.25</GrossAmount></Line-Item><Line-Item>\[/code\]... and so on.The issue is here. The people who require this XML need to open two tabs on every line, similar to this:\[code\] <Invoice-Lines> <Line> <Line-Item> ... </Line-Item> </Line> <Line> <Line-Item> ... </Line-Item> </Line> </Invoice-Lines>\[/code\]Can anyone help me with this, please ? I have no idea if it's even possible to do this using FOR XML. I've searched the whole internet for this...Thank you in advance,Nitu Cristian