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

質問
-
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使用)
回答
すべての返信
-
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について調べていたら、返事が遅くなってしまいました。
申し訳ありません。
次のようなものを作っていろいろと試していたのですが
「'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
GOCreateXMLで出来上がった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>