I have this query (which works just fine):
INSERT INTO @CreditDebitAdjustmentDetail
(
InvoiceNum,
ClaimNum,
ClaimantGLNNum,
LineNum,
BuyersPartNum,
UPCNum,
VendorPartNum,
CrossReferenceNum,
CreditDebit,
Qty,
UOM,
Price,
AdjustmentReason,
Amount
)
SELECT tbl.col.value('(InvoiceNum/text())[1]', 'VARCHAR(500)') AS InvoiceNum,
tbl.col.value('(ClaimNum/text())[1]', 'VARCHAR(50)') AS ClaimNum,
tbl.col.value('(ClaimantGLNNum/text())[1]', 'VARCHAR(50)') AS ClaimantGLNNum,
tbl2.col2.value('(LineNum/text())[1]', 'VARCHAR(500)') AS LineNum,
tbl2.col2.value('(BuyersPartNum/text())[1]', 'VARCHAR(500)') AS BuyersPartNum,
tbl2.col2.value('(UPCNum/text())[1]', 'VARCHAR(500)') AS UPCNum,
tbl2.col2.value('(VendorPartNum/text())[1]', 'VARCHAR(500)') AS VendorPartNum,
tbl2.col2.value('(CrossReferenceNum/text())[1]', 'VARCHAR(500)') AS CrossReferenceNum,
tbl2.col2.value('(CreditDebit/text())[1]', 'VARCHAR(500)') AS CreditDebit,
tbl2.col2.value('(Qty/text())[1]', 'VARCHAR(500)') AS Qty,
tbl2.col2.value('(UOM/text())[1]', 'VARCHAR(500)') AS UOM,
tbl2.col2.value('(Price/text())[1]', 'VARCHAR(500)') AS Price,
tbl2.col2.value('(AdjustementReason/text())[1]', 'VARCHAR(500)') AS AdjustmentReason,
tbl2.col2.value('(Amount/text())[1]', 'VARCHAR(500)') AS Amount
FROM @XML.nodes('/CreditDebitAdjustments/CreditDebitAdjustment') AS tbl (col)
CROSS APPLY tbl.col.nodes('Items/Item') AS tbl2 (col2);
However, for the column LineNum
, I want to INSERT an incremented number (e.g. 1, 2, 3, 4) depending on how many children there are under Items/Item INSTEAD OF the actual value there.
How would I go about doing this increment on the fly?
Did I confuse you guys?
EDIT -
XML looks like this:
<parent>
<blah1>hello1</blah1>
<blah2>hello2</blah2>
<blah3>hello3</blah3>
</parent>
<Items>
<Item></Item>
<Item></Item>
...
</Items>