how to get the iterated xml node value and insert into sql tables

[EDN Admin]

<?xml version="1.0" encoding="utf-8"?><br/>
<Root><br/>
<Array-DataNode_1><br/>
<DataNode_1><br/>
<Coupon1>nocharg</Coupon1><br/>
<Coupon1_Price>NO-CHARGE</Coupon1_Price><br/>
<Coupon1_PriceType>5</Coupon1_PriceType><br/>
<Coupon1_VariableDisclaimer xml:space="preserve Valid for VIN ending in AE549168. </Coupon1_VariableDisclaimer><br/>
<Coupon1_ExpDate /><br/>
<Coupon2>accmat</Coupon2><br/>
<Coupon2_Price>10</Coupon2_Price><br/>
<Coupon2_PriceType>3</Coupon2_PriceType><br/>
<Coupon2_VariableDisclaimer xml:space="preserve Valid for VIN ending in AE549168. </Coupon2_VariableDisclaimer><br/>
<Coupon2_ExpDate>12/03/2010</Coupon2_ExpDate><br/>
<Recom_Due1 /><br/>
<Recom_Due2 /><br/>
<Recom_Due3 /><br/>
<Recom_Due4 /><br/>
<Ltr_Date>20110215</Ltr_Date><br/>
</DataNode_1>
</Array-DataNode_1>
</Root><br/>

the above specified sample data is going to import in sql tables


the coupon node is iterated i dont know how to parse and insert into sql tables. im using the openxml method to import the xml file
i want the output as table showed below

id couponcategory price type price
1 nocharg 5 NO-CHARGE -- coupon1
node details
2 accmat 3 10
-- coupon2 node details

Query:
<br/>
INSERT INTO TouchPointCoupon(FK_touchpoint, coupon_category, price_type, price)<br/>
<br/>
<br/>
SELECT
<br/>
XMLInput.nodevalue1,<br/>
XMLInput.nodevalue2,
<br/>
XMLInput.nodevalue3,<br/>

<br/>

<br/>
<br/>
FROM OPENXML(@XMLHandle,Root/Array-DataNode_1/DataNode_1,2)<br/>
WITH<br/>
(
node1 datatype,
node2 datatype,
node3 datatype
)as XMLInput

please tell the solution .............. <hr class="sig Sudhesh. G <br/>
http://gurucoders.blogspot.com" target="_blank http://gurucoders.blogspot.com <br/>

View the full article