none
XMLデータ型列の空白の要素

    質問

  • どなたかご教示いただけないでしょうか。

    XMLデータ型の列に対する検索の手段についてです。

    ある要素名には通常は数値が入力されていますが、空白のこともあります。

    value()メソッドではNullではなく0が返されてしまうのですが、Nullを返してもらう方法はないものでしょうか。

    【データ】

    テーブル名:Data
    ID │XML
    ─────────────────
    1 │<root><elm1>100</elm1></root>
    2 │<root><elm1 /></root>
    3 │<root><elm1>200</elm1></root>

    【使ったSQL文】
    select [ID],[XML].value('(/root/elm1)[1]','float') from [dbo].[Data]

    【期待した結果】
    ID │XML
    ─────────────────
    1 │100
    2 │Null
    3 │200

    【実際の結果】
    ID │XML
    ─────────────────
    1 │100
    2 │0
    3 │200

    valueメソッドでfloat型にconvertされる?ときにNullが0になっているようなのですが、、

    2019年5月8日 7:50

すべての返信

  • こんな?

    select [ID],  [XML].value('(/root/elm1/node())[1]','float') from [dbo].[Data]
    
    select [ID],  [XML].value('(/root/elm1/text())[1]','float') from [dbo].[Data]
    
    select [ID],  [XML].value('(/root/elm1)[1] cast as xs:float?','float') from [dbo].[Data]
    
    select [ID], CASE WHEN ([XML].value('(/root/elm1)[1]','nvarchar(max)') IS NULL) THEN NULL ELSE [XML].value('(/root/elm1/text())[1]','float') END from [dbo].[Data]
    node().text()

    cast as


    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

    2019年5月8日 10:55
  • gekka様

    ありがとうございます。
    期待した結果が得られました。

    提示いただいたコードのうち、①、②、④はうまく動作させられましたが、③はfloat型では期待通り動作したものの、(質問では伏せていた)bigint型、nvarchar(max)型では単純にfloatをbigint,nvarchar(max)に置き換えただけではエラーになってしまいました。

    ヒントがいただけたのでもう少し勉強してみます。ありがとうございました。

    2019年5月8日 23:58
  • SQL Server DB での NULL を xml 上で表すには、既定では、要素が存在しない状態ということになるそうです。以下の記事を見てください。

    NULL 値が含まれる列の既定動作
    https://docs.microsoft.com/ja-jp/sql/relational-databases/xml/columns-that-contain-a-null-value-by-default

    質問者さんのケースで <root><elm1 /></root> では要素 elm1 が存在するので、value() メソッドの結果が NULL にはならず 0 になったということでしょう。

    要素が存在しない、すなわち <root></root> の場合は NULL が返ってくるはずです。

    お試しください。

    2019年5月9日 2:55
  • SurferOnWww様

    ご教示ありがとうございます。

    おっしゃるとおり要素そのものを無くせばNullが得られるようですが、仕様上要素をなくすことができないためgekka様に提示いただいた方法で進めようと思います。

    gekka様

    ③のコードについてもリンク先の cast as の説明で理解できました。cast as ~ではsqlのデータ型ではなくXQueryのデータ型の指定が必要だったと理解しました。不正なデータが入力されるケースも考慮して③を採用しようと思います。

    ありがとうございました。

    2019年5月9日 3:47