Click here to Skip to main content
14,327,358 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to check whether passed id for example "7" exists in the xml data type column "Data" in table "#MyTable" or not

My table structure is as:

CREATE TABLE #MyTable (Data XML)

INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="1" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="2" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="3" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="4" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="5" /><RECORD ID="6" /> <RECORD ID="7" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="8" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="9" /><RECORD ID="10" /></EMPLOYEE>')
INSERT INTO #MyTable VALUES('<EMPLOYEE><RECORD ID="11" /></EMPLOYEE>')

SELECT * FROM #MyTable
Posted
Updated 15-Jul-15 21:34pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

One way could be to use exist. For example
SELECT * FROM #MyTable WHERE Data.exist('/EMPLOYEE/RECORD[@ID="7"]') = 1


ADDITION

One way is to build the statement dynamically. Consider the following
DECLARE @myVar INT = 7 -- this would be your parameter
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * FROM #MyTable WHERE Data.exist(''/EMPLOYEE/RECORD[@ID="' + CAST(@myVar AS VARCHAR(100)) + '"]'') = 1'
PRINT @SQL
EXECUTE sp_executesql @stmt = @sql
   
v2
Comments
xpertzgurtej 16-Jul-15 4:31am
   
Thanks for your reply..But my required is: 7 which will be passed in stored procedure as a parameter and i want to check whether it exists in any of the row of #MyTable or not?
Wendelius 16-Jul-15 4:45am
   
See the updated answer
xpertzgurtej 16-Jul-15 5:00am
   
Thanks its working as per required..:)
Wendelius 16-Jul-15 5:11am
   
Glad it helped :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100