How to select xml data based on a condition in SQL

Question

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.

Someone vote to close.It will be helpful to understand, reason to choose close.. — kbvishnu
– kbvishnu, Commented Feb 26, 2015 at 13:40

GarethD · Accepted Answer · 2015-02-26 13:31:32Z

2

I would select directly from the XML data and just move the item number you are selecting to the nodes rather than selecting an ID then filtering by that ID. That way you already have access to the other information.

E.g.

SELECT  ProductID = x.value('ProductID[1]', 'int'),
        ProductName = x.value('ProductName[1]', 'varchar(100)'),
        GenID = x.value('GenID[1]', 'uniqueidentifier')
FROM    @myDoc.nodes('/Root/ProductDescription[2]') prod (x);

EDIT

To get the records that match a given GenID and VocID you can use the exist() method:

DECLARE @VocID UNIQUEIDENTIFIER = 'AF05E961-9BC3-4249-A4A7-C6146D6FC614',
        @GenID UNIQUEIDENTIFIER = 'D1DCAD29-08C6-401E-9A0A-2130DC5D8CD4';

SELECT  ProductID = x.value('ProductID[1]', 'int'),
        ProductName = x.value('ProductName[1]', 'varchar(100)'),
        GenID = x.value('GenID[1]', 'uniqueidentifier')
FROM    @myDoc.nodes('/Root/ProductDescription') prod (x)
WHERE   x.exist('VocID[text() = sql:variable("@VocID")]') = 1
AND     x.exist('GenID[text() = sql:variable("@GenID")]') = 1;

edited Feb 26, 2015 at 13:31

answered Feb 26, 2015 at 8:56

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kbvishnu Over a year ago

there are n number of nodes and we have the GenID and VocID only.So I need to query the xml using GenID and VocID. Considering a normal select query we have select * from table where GenID={val} and VociD={val}

GarethD Over a year ago

I am really not sure I understand your criteria, do you want the nth node, or do you want a node that matches a given GenID?

kbvishnu Over a year ago

I want a node that matches the given GenID and VocID

kbvishnu Over a year ago

Yes :) correct I modified it and thought of posting it back. Thanks a lot :)

Collectives™ on Stack Overflow

How to select xml data based on a condition in SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related