How do I use XML in the where clause with value pairs

Question

Post reply

How do I use XML in the where clause with value pairs

pierrebas

SSC Veteran

Points: 246
More actions
August 9, 2012 at 3:22 pm

#264954

Hi Guys,
I need to create a stored proc that takes an xml input parameter, which must be used in the where clause. The XML string contains languages. Each language has a LanguageId and a ProficiencyId.
The XML looks like this:
<Languages>
<Language>
<LanguageId>9</LanguageId>
<ProficiencyId>4</ProficiencyId>
</Language>
</Languages>
There is a table in the database called "Profile", which contains all the profiles (people) that I need to filter. There is a field in the Profile table called ForeignLanguageIDs, which has the same structure as the XML above. There is also a field called HomeLanguageId.
I now need to filter out profiles based on language and proficiency level. I basically need something like this:
DECLARE @Languages XML
SET @Languages = '<Languages><Language><LanguageId>9</LanguageId><ProficiencyId>4</ProficiencyId></Language></Languages>'
SELECT
[Profile].[ProfileId],
[Profile].[Languages],
[Contact].[HomeLanguageId]
FROM
[Profile]
INNER JOIN
[SystemUser] ON [Profile].[ProfileId] = [SystemUser].[SystemUserId]
INNER JOIN
[Contact] ON [SystemUser].[SystemUserId] = [Contact].[ContactId]
WHERE
[Contact].[HomeLanguageId] IN ( SELECT LanguageId FROM @Languages )
OR
(
[Profile].[Languages].[LanguageId] = ( SELECT LanguageId FROM @Languages )
AND
[Profile].[Languages].[ProficiencyId] >= ( SELECT ProficiencyId FROM @Languages )
)
Any ideas? I am stuck. Thanks in advanced!!
Regards,
Pierre
August 10, 2012 at 8:14 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

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

Dwain Camps SSC Guru Points: 86958 More actions · Answer 1

Something like this should get you close:

DECLARE @Languages XML

SET @Languages =

'<Languages>

<Language>

<LanguageId>9</LanguageId>

<ProficiencyId>4</ProficiencyId>

</Language>

</Languages>'

-- Equivalent of your: SELECT LanguageId FROM @Languages

SELECT x.value('LanguageId[1]', 'INT')

FROM (SELECT @Languages) a(l) CROSS APPLY l.nodes('Languages/Language') b(x)

-- Equivalent of your: SELECT ProficiencyId FROM @Languages

SELECT x.value('ProficiencyId[1]', 'INT')

FROM (SELECT @Languages) a(l) CROSS APPLY l.nodes('Languages/Language') b(x)

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St