No return value on using OpenXML command in SQL Server 2008

Question

This is my XML parameter that I set to @XMLSave parameter and send to a stored procedure

<ROOT>
   <P>
      <ID>123456789</ID>
      <Name>admin</Name>
    </P>
    <Group>
       <GroupCardID>14</GroupCardID>
    </Group>
</ROOT>

and I try to get ID value with this command

EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSave

but when I select values return no value

select * 
from OPENXML (@idoc,'/Root/P',2)  With(ID int)

marc_s · Accepted Answer · 2014-01-13 19:57:31Z

1

Try this:

DECLARE @XmlParameter XML = '<ROOT>
   <P>
      <ID>123456789</ID>
      <Name>admin</Name>
    </P>
    <Group>
       <GroupCardID>14</GroupCardID>
    </Group>
</ROOT>'

SELECT
    @XmlParameter.value('(/ROOT/P/ID)[1]', 'int')

I always prefer the native XQuery support over the clunky old OPENXML stuff.....

answered Jan 13, 2014 at 19:57

marc_s

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

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

1 Comment

MH2538 Over a year ago

thank you . but this is on old system that i have to fix some problem and cannot change its structure at this time

MH2538 · Accepted Answer · 2014-01-13 20:15:44Z

0

I Found the answer finally: OpenXML parameter is case sensitive : my XML value start with "ROOT" and openxml parameter was "Root"

answered Jan 13, 2014 at 20:15

MH2538

1882 gold badges4 silver badges11 bronze badges

1 Comment

MH2538 Over a year ago

yes i do. but i am working on project that was developed before me. and have to fix through OPENXML.

Collectives™ on Stack Overflow

No return value on using OpenXML command in SQL Server 2008

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related