title: How to use openXML() for Complex XMLs in SQL Server 2005
body: <p>I have the following complex XML</p>
  
  <pre><code>&lt;Collection&gt;
   &lt;VOUCHER&gt;
        &lt;DATE TYPE="Date"&gt;20110401&lt;/DATE&gt;
        &lt;NARRATION TYPE="String"&gt;MUNNA CONVENT ROAD&lt;/NARRATION&gt;
        &lt;VOUCHERTYPENAME&gt;RETAIL&lt;/VOUCHERTYPENAME&gt;
        &lt;VOUCHERNUMBER&gt;R-2-I2-9-6-27751&lt;/VOUCHERNUMBER&gt;
        &lt;ALLLEDGERENTRIES.LIST&gt;            
             &lt;LEDGERNAME&gt;U.S.T. CANTEEN&lt;/LEDGERNAME&gt;
             &lt;AMOUNT&gt;-2678.9985&lt;/AMOUNT&gt;
        &lt;/ALLLEDGERENTRIES.LIST&gt;
        &lt;ALLLEDGERENTRIES.LIST&gt;            
             &lt;LEDGERNAME&gt;U.S.T. CANTEEN&lt;/LEDGERNAME&gt;
             &lt;AMOUNT&gt;-2678.9985&lt;/AMOUNT&gt;
        &lt;/ALLLEDGERENTRIES.LIST&gt;
   &lt;/VOUCHER&gt;
   &lt;VOUCHER&gt;
        &lt;DATE TYPE="Date"&gt;20110401&lt;/DATE&gt;
        &lt;NARRATION TYPE="String"&gt;MUNNA CONVENT ROAD&lt;/NARRATION&gt;
        &lt;VOUCHERTYPENAME&gt;RETAIL&lt;/VOUCHERTYPENAME&gt;
        &lt;VOUCHERNUMBER&gt;R-2-I2-9-6-27751&lt;/VOUCHERNUMBER&gt;
        &lt;ALLLEDGERENTRIES.LIST&gt;            
             &lt;LEDGERNAME&gt;U.S.T. CANTEEN&lt;/LEDGERNAME&gt;
             &lt;AMOUNT&gt;-2678.9985&lt;/AMOUNT&gt;
        &lt;/ALLLEDGERENTRIES.LIST&gt;
        &lt;ALLLEDGERENTRIES.LIST&gt;            
             &lt;LEDGERNAME&gt;U.S.T. CANTEEN&lt;/LEDGERNAME&gt;
             &lt;AMOUNT&gt;-2678.9985&lt;/AMOUNT&gt;
        &lt;/ALLLEDGERENTRIES.LIST&gt;
   &lt;/VOUCHER&gt;
  &lt;/Collection&gt;
  </code></pre>
  
  <p>I'm saving voucher details in 1 table, <code>ALLLEDGERENTRIES.LIST</code> details in another table. </p>
  
  <p>Both tables have relation on <code>VoucherID</code>. For a particular <code>VoucherID</code> the related x3 values should be stored. In my stored procedure I'm using <code>openxml()</code>.</p>
  
  <p>Piece of my SP:</p>
  
  <pre><code>INSERT INTO SalesVoucher(AbsID,VoucherNumber,VoucherTypeName,Narration,VoucherDate)
      SELECT @AID,VOUCHERNUMBER,VOUCHERTYPENAME,NARRATION,CAST(DATE AS DATETIME)
          FROM OPENXML(@XMLHandle,'ENVELOPE/BODY/DATA/COLLECTION/VOUCHER',3) 
          WITH (    
              VOUCHERNUMBER nVarchar(200),VOUCHERTYPENAME varchar(100),NARRATION varchar(500),DATE DATETIME
              )
      SELECT @VID=@@IDENTITY
  
  INSERT INTO SalesLedger(VoucherID,LedgerName,Amount)
      SELECT @VID,LEDGERNAME,AMOUNT
          FROM OPENXML(@XMLHandle,'ENVELOPE/BODY/DATA/COLLECTION/VOUCHER/ALLLEDGERENTRIES.LIST',3)
          WITH(
              LEDGERNAME varchar(200),AMOUNT decimal(18,0)
              )
  </code></pre>
  
  <p>All values are storing in DB but the column <code>VoucherID</code> in <code>SalesLedger</code> table is same for all the rows (it should not..) as I used <code>@@IDENTITY</code> it is returning last identity value only.</p>
  
  <p>Please someone help me how to store related <code>voucherID</code> in <code>SalesLedger</code> table using <code>openxml()</code> in sql...</p>