CODEWITHSUNDEEP
sqlsql-servert-sqlopenxml
khr055
khr055

Reputation: 29032

OPENXML T-SQL not working as expected

I'm writing a stored procedure that takes Xml, but the part that parses the Xml isn't working quite right. Here's the sproc:

CREATE PROCEDURE [dbo].[SprocName]
    @Xml TEXT
AS

DECLARE @XmlHandle INT

DECLARE @Table TABLE
(
    Id INT
)

-- Process the xml for use
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @Xml

INSERT @Table 
SELECT 
    Id
FROM 
OPENXML(@XmlHandle, 'Ids', 2) 
WITH 
(
    Id INT
)

SELECT * FROM @Table

EXEC sp_xml_removedocument @XmlHandle

And the calling code and result:

OPENXML issue

I can't seem to figure out what the problem is. I am expecting that 1 and 2 will be in the temporary table, but as you can see, I only have 1. Now, I call upon the power of StackOverflow. Bless me with your wisdom!!!

Upvotes: 1

Views: 1742

Answers (1)

marc_s
marc_s

Reputation: 754488

If ever possible, avoid the OPENXML stuff - it's old, it's creaky, it gobbles up memory...

Use the built-in native XQuery support in SQL Server 2005 and up - with that, you can do something like this very easily:

DECLARE @Table TABLE (Id INT)

DECLARE @Input XML = '<Ids><Id>1</Id><Id>2</Id></Ids>'

INSERT INTO @Table(Id)
    SELECT
        IdNode.value('(.)[1]', 'int')
    FROM
        @input.nodes('/Ids/Id') AS IdTbl(IdNode)

SELECT * FROM @Table

That gives you 1, 2 as output from @table. No need to call sp_xml_preparedocument and sp_xml_removedocument - just run it!

Upvotes: 2

Related Questions