How to read value from xml in SQL Server

Question

I want to read the value in xml returned by a WCF service which gets called by a stored procedure. The code below returns a null:

declare @xmlValue  varchar(1000)
declare @responseString varchar(100)
declare @idoc int

set @xmlValue = '<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">220</string>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlValue

SELECT *
FROM OPENXML (@idoc, '/*',3)
      WITH (string  varchar(100))

EXEC sp_xml_removedocument @idoc

marc_s · Accepted Answer · 2014-02-05 07:30:45Z

1

Of course - the XML has a XML namespace that you're ignoring - you need to pay attention to that!

I would also recommend to use the native XQuery support in SQL Server.

Try this:

DECLARE @xmlvalue XML
SET @xmlValue = '<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">220</string>'

-- define the XML namespace
;WITH XMLNAMESPACES('http://schemas.microsoft.com/2003/10/Serialization/' AS ns)
SELECT
    @xmlValue.value('(ns:string)[1]', 'int')  -- and **use** the XML namespace!

answered Feb 5, 2014 at 7:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andris Mudiayi Over a year ago

thanks marc_s, i forgot to mention that my sqlserver is old...do you know how to do it in sql2000 ?

marc_s Over a year ago

@AndrisMudiayi: sorry, no, I don't know how to do this in SQL Server 2000 - this is just way to old now.

Collectives™ on Stack Overflow

How to read value from xml in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related