How can I query a value in SQL Server XML column

Question

I have following XML stored in a XML column  called Roles  in a SQL Server database    lt root gt      lt role gt Alpha lt  role gt      lt role gt Beta lt  role gt      lt role gt Gamma lt  role gt   lt  root gt    I d like to list all rows that have a specific role in them  This role passed by parameter

User · Answer

Useful tip  Query a value in SQL Server XML column  XML with namespace   e g    Table  dbo   Log XML  contains columns Parametrs  xml  TimeEdit  datetime    e g  XML in Parametrs    lt ns0 Record xmlns ns0  http   Integration  gt    lt MATERIAL gt 10 lt  MATERIAL gt    lt BATCH gt A1 lt  BATCH gt    lt  ns0 Record gt    e g  Query   select  Parametrs TimeEdit from   dbo   Log XML  where  Parametrs value       Record BATCH  1     varchar max    like   A1    ORDER BY TimeEdit DESC

User · Answer

You could do the following  declare  role varchar 100     Alpha  select   from xmltable where convert varchar max  xmlfield  like    lt role gt    role   lt  role gt      Obviously this is a bit of a hack and I wouldn t recommend it for any formal solutions  However I find this technique very useful when doing adhoc queries on XML columns in SQL Server Management Studio for SQL Server 2012

User · Answer

select   Roles from   MyTable where   Roles value    root role  1     varchar max    like  StringToSearchFor   In case your column is not XML  you need to convert it  You can also use other syntax to query certain attributes of your XML data  Here is an example    Let s suppose that data column has this   lt Utilities CodeSystems CodeSystemCodes iid  quot 107 quot  CodeSystem  quot 2 quot  Code  quot 0001F quot  CodeTags  quot -19- quot       gt       and you only want the ones where CodeSystem   2 then your query will be  select     data   from    dbo   CodeSystemCodes data     where   CAST  data  as XML  value    Utilities CodeSystems CodeSystemCodes  CodeSystem  1     varchar max       2   These pages will show you more about how to query XML in T-SQL  Querying XML fields using t-sql Flattening XML Data in SQL Server EDIT After playing with it a little bit more  I ended up with this amazing query that uses CROSS APPLY  This one will search every row  role  for the value you put in your like expression    Given this table structure  create table MyTable  Roles XML   insert into MyTable values    lt root gt      lt role gt Alpha lt  role gt      lt role gt Gamma lt  role gt      lt role gt Beta lt  role gt   lt  root gt     We can query it like this  select   from    select         pref value   text    1     varchar 32    as RoleName from         MyTable CROSS APPLY         Roles nodes   root role   AS Roles pref     as Result  where RoleName like   ga    You can check the SQL Fiddle here  http   sqlfiddle com   18 dc4d2 1 0

User · Answer

declare  T table Roles xml   insert into  T values    lt root gt      lt role gt Alpha lt  role gt      lt role gt Beta lt  role gt      lt role gt Gamma lt  role gt   lt  root gt     declare  Role varchar 10   set  Role    Beta   select Roles from  T where Roles exist   root role text       sql variable   Role        1   If you want the query to work as where col like   Beta   you can use contains  declare  T table Roles xml   insert into  T values    lt root gt      lt role gt Alpha lt  role gt      lt role gt Beta lt  role gt      lt role gt Gamma lt  role gt   lt  root gt     declare  Role varchar 10   set  Role    et   select Roles from  T where Roles exist   root role text   contains    sql variable   Role         1

User · Answer

I came up with a simple work around below which is easy to remember too    -   select   from    select cast  xmlCol as varchar max   texty  from myTable  NOLOCK     a  where texty like   MySearchText

User · Answer

I used the below statement to retrieve the values in the XML in the Sql table  with xmlnamespaces default  http   test com 2008 06 23 HL OnlineContract ValueObjects   select   from   select             OnlineContractID              DistributorID              SponsorID       RequestXML  value N  OnlineContractDS 1  Properties 1  Name 1     nvarchar 30    as  Name       RequestXML  value N  OnlineContractDS 1  Properties 1  Value 1     nvarchar 30    as  Value         RequestXML  value N  OnlineContractDS 1  Locale 1     nvarchar 30    as  Locale  from  OnlineContract   as olc where olc Name like   EMAIL   and olc Value like   EMAIL   and olc Locale  UK EN

User · Answer

In case you want to find other node besides  quot Alpha quot   the query should be something like this  select Roles from MyTable where Roles exist      root   role contains    quot Beta quot         1

User · Answer

You can query the whole tag  or just the specific value  Here I use a wildcard for the xml namespaces  declare  myDoc xml set  myDoc      lt Root xmlns xsd  quot http   www w3 org 2001 XMLSchema quot  xmlns xsi  quot http   www w3 org 2001 XMLSchema-instance quot  xmlns  quot http   stackoverflow com quot  gt       lt Child gt my value lt  Child gt    lt  Root gt    select  myDoc query     Root   Child   -- whole tag select  myDoc value      Root   Child  1     varchar 255    -- only value

User · Answer

if your field name is Roles and table name is table1 you can use following to search  DECLARE  Role varchar 50   SELECT   FROM table1 WHERE Roles exist    root role   sql variable   Role       1

[sql] How can I query a value in SQL Server XML column

Examples related to sql

Examples related to sql-server

Examples related to xml

Examples related to xquery