Search code examples
sqlsql-servert-sqlopenxml

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

Solution

  • 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!