XML型に対する問い合わせ

質問

以下のような(1)と(2)のxml型を値を持つレコードがある場合にbの中でcとdが両方1の場合だけ検索（(１)のレコードだけが欲しい）するようにはどのように問い合わせればば良いでしょうか？

(1) <a>
　　　<b><c>1</c><d>1</d></b>
　　</a>
(2) <a>
　　　<b><c>1</c><d>0</d></b>
　　　<b><c>0</c><d>1</d></b>
　　</a>

自分で考えた問い合わせだと（１）と(2)の両方のレコードが返ってきてしまいます。

select * from test
where
　　'1'=ANY(xpath('/a/b/c/text()',xml)::text[])
　　AND '1'=ANY(xpath('/a/b/d/text()',xml)::text[])

回答No.1

yamada59 · ベストアンサー

以下の SQL でどうでしょうか。

test=> SELECT * FROM test WHERE array_length(xpath('/a/b[c="1" and d="1"]', xml), 1) > 0;
xml
--------------------------
<a> +
<b><c>1</c><d>1</d></b>+
</a>
(1 行)

お礼

yamada59さん、回答ありがとうございます。

そのような指定でできるのですね。

andがxpath()で使える記述は見つけていたのですが自分で指定するとエラーになりサンプルも見つけられず悩んでいました。

ただ、array_length()はバージョン９以降のようで現状自分のサーバ(v8)で関数が無いとエラーになりました。

少しだけアレンジして次のように直しましたら、今のバージョンでも(1)のレコードだけ検索できました。

SELECT * FROM test WHERE xpath('/a/b[c="1" and d="1"]', xml)::text[]<>'{}'