Converting from OPENXML

Question

I've seen a lot of posts telling people not to use OPENXML,  but my problem is I can't work out the @XML.Value() format and get it to work.


The following works now and returns thousands of rows:

SELECT *
FROM OPENXML(@hDoc, '/Document/Invoice/CustomerNodeList/CustomerNode/ProductList/Product', 3)
WITH (
     id int '@mp:id' , parentid int '@mp:parentid',
     [ProductInstanceId] [nvarchar](30) '@ProductInstanceId',
     [AccountNumber] [nvarchar](100) '../../AccountNumber',
     [Type] [nvarchar](100) 'Type',
     [Status] [nvarchar](100) 'Status',
     [CompanionProductInstanceId] [nvarchar](30) 'Companion/@ProductInstanceId',
     [CompanionType] [nvarchar](100) 'Companion/Type',
     [CompanionStatus] [nvarchar](100) 'Companion/Status',
     [CompanionComponent] [nvarchar](100) 'Companion/Component'
)

When I tried to write this in the alternative format, I got 1 row !  If anyone could provide me with some good examples that are applicable i'd appreciate it !

Answer 1

Could you provide a small piece of sample XML and expected results please?

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx

Have a look at the nodes method in the second article.