asked on

Select from OpenXML in SQL Server only returns 1st row

This can be run in any TSQL query window. No db needed.
I am only getting the first problem as output. I need to get both problems. Thanks for the helpl

declare @xmlhandle int
declare @xmldoc varchar(8000)
set @xmldoc = '<ns0:Observe xmlns:ns0="http://Foo.XMLReport1">
<ProblemList>
<Problem>problem1 (ICD-040.1)</Problem>
<Problem>problem2 (ICD-040.2)</Problem>
</ProblemList>
</ns0:Observe>'

EXEC sp_xml_preparedocument @XMLHandle OUTPUT, @XMLDoc, '<root xmlns:a="http://Foo.XMLReport1"/>'
SELECT XMLProblem
FROM OPENXML(@XMLHandle, '//a:Observe', 3)
WITH ( XMLProblem varchar(2000) 'ProblemList/Problem')

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Have you considered using the xml type?

declare @xmldoc varchar(8000)
set @xmldoc = '<ns0:Observe xmlns:ns0="http://Foo.XMLReport1">
<ProblemList>
<Problem>problem1 (ICD-040.1)</Problem>
<Problem>problem2 (ICD-040.2)</Problem>
</ProblemList>
</ns0:Observe>'
declare @xml xml set @xml = @xmldoc

;With xmlnamespaces('http://Foo.XMLReport1' as a)
SELECT XMLProblem = n.c.value('.','varchar(2000)')
from @xml.nodes('//a:Observe/ProblemList/Problem') n(c)

That way, you won't have memory leaks if you forget the sp_xml_removedocument like in the question. Although you probably left it out just for brevity.

dpicco

ASKER

Thank you very much cyberkiwi. Your solution works. I will take a look at the xml type. It is very useful. Thanks again!