Tablodaki XML alandan varyant ve özellik veri satırı çoğaltmanın yolu hakkında

Selamlar değerli arkadaşlar;

Bir konu üzerinde bayadır bir uğraştım ve araştırdım fakat bir türlü çözmeyi başaramadım. Tablodaki xml alandan veriyi çekip çoğalması gereken satırlar oluşturamadım.

DECLARE @str nvarchar(max)
SET @str = '<root>
<item>
<id>388886</id>
<stockCode><![CDATA[GM 9690 SİYAH SÜET]]></stockCode>
<label><![CDATA[Vilma Siyah Süet Boti]]></label>
<status>1</status>
<brand><![CDATA[BUTİK]]></brand>
<brandId>3383</brandId>
<barcode></barcode>
<mainCategory><![CDATA[Sandalet]]></mainCategory>
<category><![CDATA[]]></category>
<subCategory><![CDATA[]]></subCategory>
<buyingPrice>50.000</buyingPrice>
<price1>92.500</price1>
<price2>51.514</price2>
<price3>0.000</price3>
<price4>0.000</price4>
<price5>38.000</price5>
<tax>8</tax>
<currencyAbbr>TL</currencyAbbr>
<stockAmount>1</stockAmount>
<stockType><![CDATA[Adet]]></stockType>
<warranty>0</warranty>
<picture1Path><![CDATA[]]></picture1Path>
<picture2Path><![CDATA[]]></picture2Path>
<picture3Path><![CDATA[]]></picture3Path>
<picture4Path><![CDATA[]]></picture4Path>
<picture5Path/>
<picture6Path/>
<picture7Path/>
<picture8Path/>
<dm3>3.0000</dm3>
<details><![CDATA[]]></details>
<rebate>55.46296</rebate>
<rebateType>0</rebateType>
<shortdetails><![CDATA[]]></shortdetails>
<title><![CDATA[Vilma Siyah Süet Boti]]></title>
<keywords><![CDATA[bayan,ayakkabi,ayakkabisi,stiletto,babet,platform,bot,boti,çizme,cizme,sneakers,sandalet,outlet,indirim,tarz,moda,yeni,süper,harika,kislik,kışlık,yazlık,yazlik,trend,son,sonbahar,ilkbahar,topuklu,yuksek,alcak,topuk,standart, Vilma, Siyah, Süet, Boti, Vilma Siyah, Vilma Siyah Süet, Siyah Süet Boti, Süet Boti, OUTLET, İlkbahar - Yaz Sezonu İndirim Ürünleri,]]></keywords>
<descriptions/>
<searchKeywords><![CDATA[bayan,ayakkabi,ayakkabisi,stiletto,babet,platform,bot,boti,çizme,cizme,sneakers,sandalet,outlet,indirim,tarz,moda,yeni,süper,harika,kislik,kışlık,yazlık,yazlik,trend,son,sonbahar,ilkbahar,topuklu,yuksek,alcak,topuk,standart, Vilma, Siyah, Süet, Boti, Vi]]></searchKeywords>
<seoTagName/>
<metaTitle/>
<metaDescription/>
<metaKeywords/>
<productSpecialInfoTitle/>
<productSpecialInfoContent/>
<variants>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_35]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>0</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[35]]></variantValue>
</option>
</options>
</variant>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_36]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>1</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[36]]></variantValue>
</option>
</options>
</variant>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_37]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>0</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[37]]></variantValue>
</option>
</options>
</variant>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_38]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>0</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[38]]></variantValue>
</option>
</options>
</variant>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_39]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>0</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[39]]></variantValue>
</option>
</options>
</variant>
<variant>
<vStockCode><![CDATA[GM 9690 SİYAH SÜET_40]]></vStockCode>
<vBarcode></vBarcode>
<vStockAmount>0</vStockAmount>
<vBuyingPrice>0.000</vBuyingPrice>
<vPrice1>92.500</vPrice1>
<vPrice2>51.514</vPrice2>
<vPrice3>0.000</vPrice3>
<vPrice4>0.000</vPrice4>
<vPrice5>0.000</vPrice5>
<vRebate>55.46296</vRebate>
<vRebateType>0</vRebateType>
<vDm3>3.0000</vDm3>
<options>
<option>
<variantName><![CDATA[NUMARA]]></variantName>
<variantValue><![CDATA[40]]></variantValue>
</option>
</options>
</variant>
</variants>
<specs>
<spec>
<specGroup>İşemciler</specGroup>
<specName>İşlemci Tipi</specName>
<specValue>Core i7 </specValue>
<spec>
</specs>
</item>
</root>';

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML)
-- Iterate through each of the "root\user" records in our XML
SELECT
x.Rec.query('variants/variant/vStockCode').value('.', 'nvarchar(2000)') AS 'vStockCode',

x.Rec.query('id').value('.', 'int') AS 'id',
x.Rec.query('stockCode').value('.', 'nvarchar(2000)') AS 'stockCode',
x.Rec.query('label').value('.', 'nvarchar(2000)') AS 'label',

x.Rec.query('status').value('.', 'nvarchar(2000)') AS 'status',
x.Rec.query('brand').value('.', 'nvarchar(2000)') AS 'brand',
x.Rec.query('brandId').value('.', 'nvarchar(2000)') AS 'brandId',
x.Rec.query('barcode').value('.', 'nvarchar(2000)') AS 'barcode',
x.Rec.query('mainCategory').value('.', 'nvarchar(2000)') AS 'mainCategory',
x.Rec.query('category').value('.', 'nvarchar(2000)') AS 'category',
x.Rec.query('subCategory').value('.', 'nvarchar(2000)') AS 'subCategory',
x.Rec.query('buyingPrice').value('.', 'nvarchar(2000)') AS 'buyingPrice',
x.Rec.query('price1').value('.', 'nvarchar(2000)') AS 'price1',
x.Rec.query('price2').value('.', 'nvarchar(2000)') AS 'price2',
x.Rec.query('price3').value('.', 'nvarchar(2000)') AS 'price3',
x.Rec.query('price4').value('.', 'nvarchar(2000)') AS 'price4',
x.Rec.query('price5').value('.', 'nvarchar(2000)') AS 'price5',
x.Rec.query('tax').value('.', 'nvarchar(2000)') AS 'tax',
x.Rec.query('currencyAbbr').value('.', 'nvarchar(2000)') AS 'currencyAbbr',
x.Rec.query('stockAmount').value('.', 'nvarchar(2000)') AS 'stockAmount',
x.Rec.query('stockType').value('.', 'nvarchar(2000)') AS 'stockType',
x.Rec.query('warranty').value('.', 'nvarchar(2000)') AS 'warranty',
x.Rec.query('picture1Path').value('.', 'nvarchar(2000)') AS 'picture1Path',
x.Rec.query('picture2Path').value('.', 'nvarchar(2000)') AS 'picture2Path',
x.Rec.query('picture3Path').value('.', 'nvarchar(2000)') AS 'picture3Path',
x.Rec.query('picture4Path').value('.', 'nvarchar(2000)') AS 'picture4Path',
x.Rec.query('picture5Path').value('.', 'nvarchar(2000)') AS 'picture5Path',
x.Rec.query('picture6Path').value('.', 'nvarchar(2000)') AS 'picture6Path',
x.Rec.query('picture7Path').value('.', 'nvarchar(2000)') AS 'picture7Path',
x.Rec.query('picture8Path').value('.', 'nvarchar(2000)') AS 'picture8Path',
x.Rec.query('dm3').value('.', 'nvarchar(2000)') AS 'dm3',
x.Rec.query('details').value('.', 'nvarchar(2000)') AS 'details',
x.Rec.query('rebate').value('.', 'nvarchar(2000)') AS 'rebate',
x.Rec.query('rebateType').value('.', 'nvarchar(2000)') AS 'rebateType',
x.Rec.query('shortdetails').value('.', 'nvarchar(2000)') AS 'shortdetails',
x.Rec.query('title').value('.', 'nvarchar(2000)') AS 'title',
x.Rec.query('keywords').value('.', 'nvarchar(2000)') AS 'keywords',
x.Rec.query('descriptions').value('.', 'nvarchar(2000)') AS 'descriptions',
x.Rec.query('searchKeywords').value('.', 'nvarchar(2000)') AS 'searchKeywords',
x.Rec.query('seoTagName').value('.', 'nvarchar(2000)') AS 'seoTagName',
x.Rec.query('metaTitle').value('.', 'nvarchar(2000)') AS 'metaTitle',
x.Rec.query('metaDescription').value('.', 'nvarchar(2000)') AS 'metaDescription',
x.Rec.query('metaKeywords').value('.', 'nvarchar(2000)') AS 'metaKeywords',
x.Rec.query('productSpecialInfoTitle').value('.', 'nvarchar(2000)') AS 'productSpecialInfoTitle',
x.Rec.query('productSpecialInfoContent').value('.', 'nvarchar(2000)') AS 'productSpecialInfoContent',

x.Rec.query('variants/variant/vStockCode').value('.', 'nvarchar(2000)') AS 'vStockCode',
x.Rec.query('variants/variant/vBarcode').value('.', 'nvarchar(2000)') AS 'vBarcode',
x.Rec.query('variants/variant/vStockAmount').value('.', 'nvarchar(2000)') AS 'vStockAmount',
x.Rec.query('variants/variant/vBuyingPrice').value('.', 'nvarchar(2000)') AS 'vBuyingPrice',
x.Rec.query('variants/variant/vPrice1').value('.', 'nvarchar(2000)') AS 'vPrice1',
x.Rec.query('variants/variant/vPrice2').value('.', 'nvarchar(2000)') AS 'vPrice2',
x.Rec.query('variants/variant/vPrice3').value('.', 'nvarchar(2000)') AS 'vPrice3',
x.Rec.query('variants/variant/vPrice4').value('.', 'nvarchar(2000)') AS 'vPrice4',
x.Rec.query('variants/variant/vPrice5').value('.', 'nvarchar(2000)') AS 'vPrice5',
x.Rec.query('variants/variant/vRebate').value('.', 'nvarchar(2000)') AS 'vRebate',
x.Rec.query('variants/variant/vRebateType').value('.', 'nvarchar(2000)') AS 'vRebateType',
x.Rec.query('variants/variant/vDm3').value('.', 'nvarchar(2000)') AS 'vDm3',

x.Rec.query('variants/variant/options/option/variantName').value('.', 'nvarchar(2000)') AS 'variantName',
x.Rec.query('variants/variant/options/option/variantValue').value('.', 'nvarchar(2000)') AS 'variantValue',
x.Rec.query('specs').value('.', 'nvarchar(2000)') AS 'specs'

FROM
@xml.nodes('/root/item') as x(Rec)
GO

Normalde yukardaki resimlerdeki satırlar varyantların ve özelliklerin çokluğuna göre 1den fazla olması gerekiyordu. Yukardaki sql kodunda nasıl bir değişiklik yapabilirim ?