XML 作为存储过程中的参数(sql server)

问题描述：

我有一个带有 XML 参数的存储过程.

I have a stored procedure with a parameter in XML.

我的问题是关于 XML 的格式.

My problem is about the format of XML.

此解决方案有效:

<ROOT><ids><id>2013-01-01</id></ids><ids><id>2013-01-02</id></ids></ROOT>

SELECT * FROM OPENXML(@handle, '/ROOT/id') WITH (idDate Date)

结果:2013-01-01 .. 2013-01-02

但是第二个解决方案不行，为什么?

But the second solution not, why?

<ROOT><id>2013-01-01</id><id>2013-01-02</id></ROOT>

SELECT * FROM OPENXML(@handle, '/ROOT') WITH (idDate Date)

结果:Null

XML 格式良好，不是吗?

The XML is well formed, not?

答

您声称有效的第一个查询实际上不适用于您提供的 XML.应该是这样的.

Your first query that you claim work does in fact not work with the XML you provided. It should be like this.

declare @handle int
declare @XML xml = '<ROOT><ids><id>2013-01-01</id></ids><ids><id>2013-01-02</id></ids></ROOT>'
exec sp_xml_preparedocument @handle out, @XML
select * from openxml(@handle, '/ROOT/ids', 2) with (id Date)
exec sp_xml_removedocument @handle

第二个版本应该是

declare @handle int
declare @XML xml = '<ROOT><id>2013-01-01</id><id>2013-01-02</id></ROOT>'
exec sp_xml_preparedocument @handle out, @XML
select * from openxml(@handle, '/ROOT/id', 2) with (id Date '.')
exec sp_xml_removedocument @handle

由于您使用的是 SQL Server 2008 或更高版本，因此您可以改用 XML 数据类型.

Since you are using SQL Server 2008 or later you could use the XML datatype instead.

declare @XML xml = '<ROOT><id>2013-01-01</id><id>2013-01-02</id></ROOT>'

select T.N.value('text()[1]', 'date') as id
from @XML.nodes('ROOT/id') as T(N)

XML 作为存储过程中的参数(sql server)

相关推荐