1

I am developing an sql server 2012 based application. I am a newbie to SQl server. But the requirement is to use it. One of the table I am using contains an XML Datatype column. However the data containing in that column may vary as per the xml element. The only thing in common is the root: For instance this is a sample data:

<Tags>
    <key1>Value1</key1>
    <key2>Value2</key2>
    <key3>Value3</key3>
    <key4>Value4</key4>
    <key5>Value5</key5>
    <key6>Value6</key6>
</Tags>

What I want to do is to query the whole table and fetch records that will match a specific key and a specific values sent by the user.

Please assist me.

Steve Pettifer
  • 1,975
  • 1
  • 19
  • 34
Arsene
  • 1,037
  • 4
  • 20
  • 47
  • 2
    Please refer [this](http://stackoverflow.com/questions/4815836/how-do-you-read-xml-column-in-sql-server-2008) , [this](http://stackoverflow.com/questions/13195922/how-to-query-xml-column-in-tsql) and [this](http://stackoverflow.com/questions/2988122/how-to-query-on-xml-column-in-sql-server-2008) will give you a starting point. – huMpty duMpty Apr 08 '14 at 08:15
  • @huMptyduMpty: With the examples the xml element to query is known. In my case I do not even know which element is going to be queried. I do agree that the examples has given a clue on what can be done. – Arsene Apr 08 '14 at 08:52

1 Answers1

1

Well it sounds like you need to use some variables to build an XQuery, yes? So, assuming you build a stored procedure or something which takes a pair of string arguments for key and value you could use the following example I've knocked up in SQL Fiddle so you can try it out.

DECLARE @key nvarchar(20)
DECLARE @value nvarchar(20)
SET @key = N'key5'
SET @value = N'Value5'

SELECT 
    TagValue = T1.xmlcol.value('(/Tags/*[local-name()=sql:variable("@key")])[1]', 'varchar(10)')
FROM
    dbo.T1
WHERE 
    T1.xmlcol.exist('/Tags/*[local-name()=sql:variable("@key")][text() = "Value5"]') = 1
Steve Pettifer
  • 1,975
  • 1
  • 19
  • 34
  • Although SQLFiddle has just died - hopefully not because of me! But when it's back up, it contains a working example including your example XML above from which you should be able to extrapolate a solution that works for you. – Steve Pettifer Apr 08 '14 at 09:41
  • Cool. Would appreciate it if you accept the answer if it does the trick :) – Steve Pettifer Apr 08 '14 at 11:34