April 23, 2010 at 10:13 am
Hi, I have following XML variable.I m trying to get the promotion Code from the variable for the particular item in the xml. I tried a lot but
could not get that. That would be great if someone could help me out in this!
here is the xml variable @LineItemsXML =
<ArrayOfLineItem>
<LineItem>
<Product>
<Itemnmbr>ITEM1</Itemnmbr>
<TaxFlag>false</TaxFlag>
</Product>
<PromotionCodes>
<PromotionCode>
<PromotionCodeID>0</PromotionCodeID>
<PromotionCode>Promo1</PromotionCode>
</PromotionCode>
<PromotionCode>
<PromotionCodeID>0</PromotionCodeID>
<PromotionCode>Promo2</PromotionCode>
</PromotionCode>
</PromotionCodes>
</LineItem>
<LineItem>
<Product>
<Itemnmbr>Item2</Itemnmbr>
<TaxFlag>false</TaxFlag>
</Product>
<PromotionCodes nil="true" />
</LineItem>
</ArrayOfLineItem>
I have cursor for lineitems, so for each lineitem we have several promotion codes.
What I m trying here is, I wanted to get promotionCode for each lineitem.
SET @Itemnmbr = @LINEITEMS.value('(/LineItem/Product/Itemnmbr)[1]','varchar(10)')
SELECT PromoCodes.PromoCode.query('.')
FROM @LineItemsXML.nodes('/ArrayOfLineItem/LineItem/PromotionCodes/*') PromoCodes(PromoCode)
where @LineItemsXML.value('(/LineItem/Product/Itemnmbr)[1]','varchar(10)') = @ITEMNMBR
But this is not working !! Please help..
Thank you
April 23, 2010 at 10:45 am
What is the reason behind the requirement to loop through the XML?
You could get a table based on the xml:
SELECT
Z.Itemnmbr,
Z.Node.value('PromotionCode[1]','varchar(30)') AS PromotionCode
FROM
(SELECT
w.value('Itemnmbr[1]','varchar(100)') AS Itemnmbr,
X.Y.query('.') AS Node
FROM @LineItemsXML.nodes('/ArrayOfLineItem/LineItem') T(u)
CROSS APPLY u.nodes('Product') V(w)
CROSS APPLYu.nodes('PromotionCodes/PromotionCode/PromotionCode') X(y)
)Z
/* result set
ItemnmbrPromotionCode
ITEM1Promo1
ITEM1Promo2
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply