Share via

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

Comments

  • Anonymous
    March 08, 2006
    If anyone's interested, as it turns out I solved this one on my own.  Apparently to select the inner text of an Xml Element you need to specify text() as the xpath

    my original way...
    FROM OPENXML(@docHandle, 'Root/Property/ID', 3) WITH (ID int)

    the correct way which works...
    FROM OPENXML(@docHandle, 'Root/Property/ID', 3) WITH (ID int 'text()')

    Anway FWIW hopefully this will help someone else down the road.  :)

  • Anonymous
    April 07, 2006
    This would also have worked: SELECT * FROM OPENXML(@docHandle, 'Root/Property/ID', 3) WITH (ID int '.')

  • Anonymous
    July 04, 2007
    How about if you will be having mutiple values from the xmlfile?

  • Anonymous
    July 30, 2008
    For multiple values you alter as follows SELECT * FROM OPENXML(@docHandle, 'Root/Property', 3) WITH (ID int 'ID/.', Val2 int 'Val2/.') Then just continue defining the nodes in the WITH statement

  • Anonymous
    June 15, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24253