ストアドプロシージャーのパラメータでXmlを使用する方法

Question

SQL Server 2005 でストアドプロシージャーのパラメータに配列を渡す方法について質問があります。

 

もともと使用していたシステムがOracleを使用していたのでパラメータに配列を渡すことが可能でしたが
この度、SQL Server 2005 へ移行することとなり、調べてみたら配列を渡すことができないという記事を見かけました。
いくつか代案があるようなのですが、その中でもXMLとしてストアドプロシージャーに渡すという方法を
採用してみようと思うのですが、いまいち実装方法がわかりません。

 

・VB.NET側でXmlパラメータにどのような形のものをセットするれば良いかの？
        objCmd.parameters.Add("@DATA",SqlDbType.Xml).Value = ???
  ・ストアド側で受け取ったXmlデータをどのように利用できるのか？
        データの使用方法は受け取った一行ずつのデータをInsert/Updateしたいだけです。

Xmlデータとして渡したいデータは次のようなものです。
※この部分はDataGridViewコントロールを使用しています。

 

行No 商品コード 名称      数量
  001   0001          商品①  1
  002   0002          商品②  1
  003   0003          商品③  1

 

初歩的な質問で申し訳ありませんが、アドバイス頂けると助かります。

宜しくお願い致します。

開発環境 VS2005(VB.NET使用)

Answer 1

sp_xml_preparedocument,sp_xml_removedocument システムストアドプロシージャとOPENXML句を使うのではないでしょうか。

さや さんからの引用

・VB.NET側でXmlパラメータにどのような形のものをセットするれば良いかの？         objCmd.parameters.Add("@DATA",SqlDbType.Xml).Value = ???

sp_xml_preparedocumentが受け取れるデータ型ならばなんでもいいと思います。

 

さや さんからの引用

・ストアド側で受け取ったXmlデータをどのように利用できるのか？         データの使用方法は受け取った一行ずつのデータをInsert/Updateしたいだけです。

sp_xml_preparedocumentでXMLをメモリに展開し、OPENXML句でそれをテーブルのように扱えます。sp_xml_removedocument で解放。

 

詳しくはドキュメント見たり検索してください。

OPENXMLで検索

Answer 2

かめたろ様、早速のアドバイスありがとうございます！！

OPENXMLについて調べていたら、返事が遅くなってしまいました。

申し訳ありません。

 

次のようなものを作っていろいろと試していたのですが

「'TEST'付近に不適切な構文があります。」というエラーになってしまいます。

CreateXMLで出来たXML自体をストアドに貼り付けて実行してみるとエラーはないのですが

VBからの実行でエラーが発生してしまいます。

どの辺りに問題があるのか、宜しければご意見頂けないでしょうか？

宜しくお願いします。

 

VB.NET側

Using cmd As New SqlCommand

    Dim cnt As Integer
      Dim par As SqlClient.SqlParameter
          cmd.Connection = con
          cmd.CommandText = CommandType.StoredProcedure
          cmd.CommandText = "TEST"
          par = cmd.Parameters.Add("@doc", SqlDbType.NVarChar)
          cmd.Parameters("@doc").Value = CreateXML()

        cnt = cmd.ExecuteNonQuery   <=== ここでエラー
  End Using

 

ストアド側

CREATE PROCEDURE [dbo].[TEST]
  @doc nvarchar(4000)
  AS
  BEGIN
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    INSERT INTO Test1 SELECT * FROM OPENXML(@idoc, 'ROOT/Orders') WITH Test1
    EXEC sp_xml_removedocument @idoc
  END
  GO

 

CreateXMLで出来上がったXML

<ROOT>
  <Orders OrderID="10248" CustomerID="VINET" OrderDate="1996/07/04 0:00:00" >
  </Orders>
  <Orders OrderID="10249" CustomerID="TOMSP" OrderDate="1996/07/05 0:00:00" >
  </Orders>
  </ROOT>

Answer 3

自己レスです。

 

エラーの原因は

cmd.CommandText = CommandType.StoredProcedure

でした。

cmd.CommandType = CommandType.StoredProcedure

と修正して無事成功しました！！

 

お騒がせして申し訳ありません。

お付き合い頂きありがとうございました。