XML 作为存储过程中的参数(sql server) [英] XML as parameter in stored procedure (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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！