In processing some XML into an SqlServer db it can often be useful to have the element name or the parent element name output, depending on how your xml data is organised.
Below is an example of using meta properties in MS SQL Server for just this purpose.
DECLARE @RESPONSE XML
DECLARE @XmlDocumentHandle int
set @RESPONSE = '
PRINT '**DEBUG ** : Preparing xml doc for response.'
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @RESPONSE
from OPENXML (@XmlDocumentHandle, '/frag/foos/*/bar', 2)
whichFoo varchar(50) '@mp:parentlocalname',
whichBar varchar(50) '@mp:localname',
bar_name varchar(50) 'name',
bar_desc varchar(50) 'desc'
) AS foobars
-- Remove the internal representation.
EXEC sp_xml_removedocument @XmlDocumentHandle
whichFoo WhichBar bar_name bar_desc
foo1 bar name1 desc 1
foo2 bar name2 desc 2
It is worth noting perhaps that these to meta properties can only be used in the context of the root element as specified by the path in the Openxml function call.
You can’t for instance retrieve the value of “whichBar” from the above example by doing something like
nameParent varchar(50) 'name/@mp:parentlocalname'
In my case I just altered the xml to have a different/better(?) structure, but still in future I’m sure I will find a use for this.