How to query XML Data type column in Sql Server

Question

4.00/5 (1 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:

SQL

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 15-Jul-15 21:26pm

xpertzgurtej

Updated 15-Jul-15 21:34pm

Wendelius

v2

Add a Solution

1 solution

Add a Solution

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

Wendelius · Accepted Answer · 2015-07-15T21:48:00

Solution 1

One way could be to use exist. For example

SQL

SELECT * FROM #MyTable WHERE Data.exist('/EMPLOYEE/RECORD[@ID="7"]') = 1

ADDITION

One way is to build the statement dynamically. Consider the following

SQL

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

Posted 15-Jul-15 21:48pm

Wendelius

Updated 15-Jul-15 22:45pm

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 :)

How to query XML Data type column in Sql Server

1 solution

Solution 1

Add your solution here

Preview 0

How to query XML Data type column in Sql Server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us