0

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>
JJ.
  • 9,580
  • 37
  • 116
  • 189
  • Can you set up a [SQL Fiddle](http://sqlfiddle.com) so we don't have to guess and make up data? – Aaron Bertrand Jan 27 '14 at 21:11
  • I updated the OP with a sample of the XML. – JJ. Jan 27 '14 at 21:13
  • 1
    That doesn't really help, I still have no idea what data you have and how to apply 1,2,3,4 etc. If you'd like help, please provide more specific sample data *and* desired results. The insert part is also quite irrelevant, and it will be much easier to provide help with a select (you can add the insert after you verify that the select gives the right results). – Aaron Bertrand Jan 27 '14 at 21:18

0 Answers0