SQL Server ある列のレコード内の特定の文字を含む部分だけを抜き出し新しい列に保存したい

Question

SQL Server ある列の各レコード内にXMLフォーマットで行数23行ぐらいのデータが格納されています。カラムのデータタイプ自体はvarcharで指定されております。
この各レコード行から<messageKey>XXXX</messageKey>ような特定タグの開始と終了部分の行のみを抜き出し、新しい別カラムとして保存したいのですが、カラムがXMLデータタイプではないためかXMLデータ操作用の関数がクエリで使えない事が分かりました。また以下のようなwhere句を使ってもレコード全体が取れてしまい目的の情報だけをうまく抜き出す事ができておりません。このようなタグを含むデータが数万行とかなり多くヒットするため少ない必要な情報だけを一覧化したいと思ったのがきっかけです。

select * from テーブル名　where　列名 like '%<messageKey>%'

どなたか何か良い方法があればご教授頂けますでしょうか？

Answer 1

カラムがXMLデータタイプではないためかXMLデータ操作用の関数がクエリで使えない事が分かりました。

XML型にCASTしても使えないのでしょうか？それともCASTでエラーになるのでしょうか？
XML データのインスタンスの作成

必要な情報だけを一覧化したい

CASTが可能なら、XQueryでの検索も可能です。
XQuery 言語リファレンス (SQL Server)

Comments

[minhouse10]

sazi様、ご回答頂き、ありがとうございます！ご指摘の通りCastする事を考えましたが、レコード数が多いため、試すのを躊躇しておりましたが、簡単なテーブルをテスト環境に作ってCASTしてからXML関数を使う方法ためしてみます！

Answer 2

こんにちは！
とりあえず、こんな感じで提示されたタグの抜出
閲覧だけなら新しい別カラムとして保存するのではなくViewを生成して閲覧できるようにしておいたほうがよいかと思います。

select substring(列名,charindex('<messageKey>',列名),charindex('</messageKey>',列名) + len('</messageKey>') - charindex('<messageKey>',列名)) from テーブル名 where patindex('%<messageKey>%</messageKey>%',列名) <> 0

<messageKey></messageKey>が0～1つ存在することを前提とします。
SQLServer2012で確認しています。

Comments

[minhouse10]

mikupedia様、ご回答頂き、ありがとうございます！substring関数の利用は考えましたが、このようなViewの生成は思いつきもしませんでした。早速試してみます！

[alg]

うーん、SQL Server についての質問なのに「PostgreSQLで動作確認しています」というのは、ちょっといかがなものでしょうか。
現に後半の `substring(列名,'(<messageKey>.*</messageKey>)')` はSQL Serverでは使えませんし。

[alg]

参考：https://docs.microsoft.com/ja-jp/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017

[mikupedia]

失礼しました、追記が蛇足となってしまいましたので削除しました。