Select from OpenXML in SQL Server only returns 1st row

dpicco
dpicco used Ask the Experts™
on
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')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:

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/ProblemList/Problem', 3)
      WITH (XMLProblem varchar(2000) '.')
EXEC sp_xml_removedocument @XMLHandle

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial