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

  • 質問

  • 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使用)

    2007年5月23日 5:09

回答

  • 自己レスです。

     

    エラーの原因は

    cmd.CommandText = CommandType.StoredProcedure

    でした。

    cmd.CommandType = CommandType.StoredProcedure

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

     

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

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

    2007年5月24日 8:19

すべての返信

  • 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で検索

    2007年5月23日 6:49
  • かめたろ様、早速のアドバイスありがとうございます!!

    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>

    2007年5月24日 7:48
  • 自己レスです。

     

    エラーの原因は

    cmd.CommandText = CommandType.StoredProcedure

    でした。

    cmd.CommandType = CommandType.StoredProcedure

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

     

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

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

    2007年5月24日 8:19