[解決済み] SQL Server の XML カラムの値を問い合わせるには？

質問

次のようなXMLがXMLカラム( Roles という名前の）SQL Server データベースに保存されています。

<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>

特定のロールを持つ行をすべてリストアップしたい。このロールはパラメータで渡されます。

どのように解決するのですか？

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

カラムが XML でない場合は、変換する必要があります。また、XMLデータの特定の属性を問い合わせるために、他の構文を使用することもできます。以下はその例です。

データカラムがこのようなものであったとします。

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... そして、あなたが欲しいのは CodeSystem = 2 であれば、あなたのクエリはこうなります。

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

これらのページでは、T-SQLでXMLをクエリする方法について詳しく説明します。

t-sqlを使用したXMLフィールドのクエリ

SQL Server における XML データの平坦化

EDIT

もうちょっと遊んでみたら、こんな素晴らしいクエリができました。 CROSS APPLY . このクエリは、すべての行（ロール）に対して、式で指定した値を検索します。

このテーブル構造を考えると

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

このように問い合わせることができます。

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%'

SQL Fiddleはこちらで確認できます。 http://sqlfiddle.com/#!18/dc4d2/1/0