XML In Where Clause

Question

XML In Where Clause

pierrebas

SSC Veteran

Points: 246
More actions
November 23, 2012 at 6:30 am

#265151

Hi,
I am trying to search for rows by comparing a field (xml) in a table to an XML input parameter, but I cannot get it to work and would appreciate all the help I can get.
CREATE TABLE [dbo].[Profile](
[ProfileId] [uniqueidentifier] NOT NULL,
[RelationshipTypeIDs] [xml] NULL
CONSTRAINT [PK__Profile__290C88E4361203C5] PRIMARY KEY CLUSTERED
(
[ProfileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Profile] (ProfileId, RelationshipTypeIDs) VALUES ('{BF91D686-83BF-4C5B-8240-C8D89EE5FF8B}','<RelationshipTypeIDs><id>6</id><id>7</id></RelationshipTypeIDs>')
INSERT [dbo].[Profile] (ProfileId, RelationshipTypeIDs) VALUES ('{BD765112-44A9-4FA8-8EB2-AC745579FCC5}','<RelationshipTypeIDs><id>1</id><id>2</id></RelationshipTypeIDs>')
INSERT [dbo].[Profile] (ProfileId, RelationshipTypeIDs) VALUES ('{DEC143C2-730B-45DA-AF95-748D9689631A}','<RelationshipTypeIDs><id>4</id><id>3</id></RelationshipTypeIDs>')
INSERT [dbo].[Profile] (ProfileId, RelationshipTypeIDs) VALUES ('{CC5B076F-5FCC-4B8B-889C-3B18BDA9C72A}','<RelationshipTypeIDs><id>1</id><id>8</id></RelationshipTypeIDs>')
DECLARE @RelationshipTypeIDs XML
SET @RelationshipTypeIDs = '<RelationshipTypeIDs><id>1</id><id>3</id></RelationshipTypeIDs>'
-- Then I need something like this:
SELECT ProfileId FROM [Profile] WHERE RelationshipTypeIDs.ID exists in @RelationshipTypeIDs;
--OUTPUT SHOULD BE:
-- {BD765112-44A9-4FA8-8EB2-AC745579FCC5}
-- {DEC143C2-730B-45DA-AF95-748D9689631A}
-- {CC5B076F-5FCC-4B8B-889C-3B18BDA9C72A}

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 1

I'm fairly new to XML myself and could use a lot more practice... the code below seems to work but your XML structure has 2 nodes that are named the same(id)... I had to change it to id1 and id2 to make this work...

DECLARE @RelationshipTypeIDs XML

declare @xdoc int

declare @ID1 varchar(5), @ID2 varchar(5)

declare @sql as varchar(4000)

SET @RelationshipTypeIDs = '<RelationshipTypeIDs><id1>1</id1><id2>3</id2></RelationshipTypeIDs>'

exec sp_xml_preparedocument @xdoc OUTPUT, @RelationshipTypeIDs

Select @ID1 = id1, @id2 = id2 from openxml(@xdoc, '/RelationshipTypeIDs',2)

with (id1 varchar(5)

,id2 varchar(5)

)

exec sp_xml_removedocument @xdoc

set @sql = '

select ProfileID

from [Profile]

where RelationshipTypeIDs.exist(''/RelationshipTypeIDs/id[. = "' + @ID1 +'"]'') = 1

or RelationshipTypeIDs.exist(''/RelationshipTypeIDs/id[. = "'+ @ID2 +'"]'') = 1'

exec(@SQL)

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

WITH CTE1 AS (

SELECT [ProfileId],

x.r.value('.','INT') AS id

FROM [Profile]

CROSS APPLY RelationshipTypeIDs.nodes('RelationshipTypeIDs/id') AS x(r)),

CTE2 AS (

SELECT x.r.value('.','INT') AS id

FROM @RelationshipTypeIDs.nodes('RelationshipTypeIDs/id') AS x(r)

)

SELECT DISTINCT c1.[ProfileId]

FROM CTE1 c1

WHERE EXISTS(SELECT * FROM CTE2 c2 WHERE c2.id=c1.id);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

pierrebas SSC Veteran Points: 246 More actions · Answer 3

You are a star!!! Thank you so much, it works like a bomb!!!

pierrebas SSC Veteran Points: 246 More actions · Answer 4

Thanks "Ten Centuries", that's exactly what I was looking for, a clean solution!!!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

pierrebas (11/23/2012)
Thanks "Ten Centuries", that's exactly what I was looking for, a clean solution!!!

You're welcome. Thanks for the feedback.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537