CODEWITHSUNDEEP
sqlsql-server-2008-r2openxml
MH2538
MH2538

Reputation: 188

No return value on using OpenXML command in SQL Server 2008

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)

Upvotes: 0

Views: 420

Answers (2)

MH2538
MH2538

Reputation: 188

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

Upvotes: 0

marc_s
marc_s

Reputation: 754348

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

Upvotes: 1

Related Questions