CODEWITHSUNDEEP
sql-serverxmlstored-procedures
user2024475
user2024475

Reputation: 343

XML as parameter in stored procedure (sql server)

I have a stored procedure with a parameter in XML.

My problem is about the format of XML.

This solution its works:

<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)

Result: 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)

Result: Null

The XML is well formed, not?

Upvotes: 6

Views: 44406

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

The second version should be

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

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)

Upvotes: 17

Related Questions