Below is my xml
DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc =
'<Root>
<ProductDescription>
<ProductID>1</ProductID>
<ProductName>Road Bike</ProductName>
<GenID>0C866AE2-7AAA-474F-8794-7538986268AE</GenID>
<VocID>AF05E961-9BC3-4249-A4A7-C6146D6FC614</VocID>
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
<ProductDescription>
<ProductID>2</ProductID>
<ProductName>Road Bike2</ProductName>
<GenID>D1DCAD29-08C6-401E-9A0A-2130DC5D8CD4</GenID>
<VocID>AF05E961-9BC3-4249-A4A7-C6146D6FC614</VocID>
<Features>
<Warranty>2 year parts and labor</Warranty>
<Maintenance>4 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
SET @ProdID = @myDoc.value('(/Root/ProductDescription/ProductID)[2]', 'int' )
SELECT @ProdID
This will give me the ID for second item. If I change I have the GenID
and VocID
. Using that I need to query and get the other data. Kindly help me to generate query for the same.I am using SQL Server 2012. Thanks in advance.
No comments:
Post a Comment