Anyone know how to use OPENXML?

[vbmom]

I've searched on "OPENXML" and come up with nothing on this forum, so I may be the expert around here. God help us.

I know how to use the SQL sp_preparedocument and sp_removedocument (or whatever they're called). I've gotten as far as getting OPENXML to retrieve two attributes from each node in my document and return a rowset from a doc that looks like:

root
-record
---attribute1
---attribute2
-record
---attribute1
---attribute2

etc.

I'm looking for examples of how to read more complicated documents that might have nested nodes. For example:

root
-record
---attribute1
---attribute2
-----node1
-------attribute3
-------attribute4
---------node2
------------attribute5
-record
... (more of the same)

JoshT where are you? You seem like the XMLpert around here.

[vbmom]

For anyone who cares, I found out a lot about OPENXML in SQL:

You need to get the text of your xml doc into some kind of text, char, or varchar storage area. SQL really s**Ks at reading a large text file (unless someone out there wants to tell me how??) into storage, so you're limited to some other means besides reading a file from SQL server.

The sp_xml_preparedocument stored procedure does something to store the text somewhere else and returns a handle required by OPENXML.

OPENXML has a parameter which allows you to begin searching for elements as if you were searching the explorer tree.

For example:

Select * from OPENXML(@hDoc,'root/customer', 2)

would return rows starting with info from each customer node. The 2 means "element centric".

Select * from OPENXML(@hDoc,'root/customer/order', 2)

would return rows starting with info from each order node.