OPENXML custom UDF (inline table function)

Ever had the need to manipulate XML programmatically in SQL? How about this generic function? Of course it needs some alterations to suit your specific needs more appropriately... It recieves one parameter - the document handle of an internal representation of an XML document (see example below).

create function dbo.fnReturn_XML_asUniversalTable
 (
 @xmlObjectHandler int
 )
returns table
as
return (
 select OX.[id] as [id]
  ,OX.parentid as parentid
  ,OX.nodetype as nodetype
  ,OX.localname as localname
  ,OX.prefix as prefix
  ,OX.namespaceuri as namespaceuri
  ,OX.datatype as datatype
  ,OX.prev as prev
  ,OX.[text] as [text]
  from openxml(@xmlObjectHandler, '/') OX
 )
go

An example of use:

declare @xml   nvarchar(4000)
declare @xmlObjectHandler int

set @xml = 'FirstNameMatijaLastNameLah'

-- Prepare the internal representation of your XML
exec dbo.sp_xml_preparedocument
  @xmlObjectHandler output
  ,@xml

select *
 from dbo.fnReturn_XML_asUniversalTable(@xmlObjectHandler)

-- Never forget to remove the document and release the handle!
exec dbo.sp_xml_removedocument
  @xmlObjectHandler

Now go and customize! ML