using the T-SQL OPENXML command

  • Article

Hello once again.  :)  I've been getting great feedback from everyone that stumbles across my blog entries so I figured I'd post yet another of my great mysteries of coding...

Today I'm trying to use OPENXML to shread an Xml document for the data I need.  For illustation purposes, here's an example of what I'm trying to do...

 

DECLARE

@docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT,
'<Root>
<Property>
<ID>182467</ID>
<ID>189037</ID>
<ID>192100</ID>
<ID>192957</ID>
<ID>194785</ID>
</Property>
</Root>'
SELECT *
FROM OPENXML(@docHandle, 'Root/Property/ID', 3) WITH (ID int)

The return is...

ID
----------------------
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL

The correct number of rows, but not the correct data in the inner text of the element. This is the first time I've used OPENXML and everything "looks" correct, but if anyone has a suggestion, as always I'm all eyes. :)