Extract the XPath of every node from an XML. Uh, in SQL Server

Disclaimer: this is SQL Server 2005 compliant! =O

Ok, we’re on a database and we want tables:

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT 
    ISNULL(id,'') AS id
    , parentid
    , ROW_NUMBER() OVER (
	PARTITION BY parentid,localname
	ORDER BY id
    ) AS idx
    , ROW_NUMBER() OVER (
	PARTITION BY parentid
	ORDER BY id
    ) AS position
    , nodetype
    , CASE
	WHEN (nodetype = 3) THEN 'text()'
	WHEN (nodetype = 2) THEN '@' + localname
	ELSE localname
    END as localname
    , text
  INTO #nodetree
  FROM OPENXML(@idoc, '/', 3)
EXEC sp_xml_removedocument @idoc

ALTER TABLE #nodetree ADD PRIMARY KEY (id)
SELECT * FROM #nodetree

Now let’s use a recursive CTE to build the XPath of every text node

;WITH cte AS (
  SELECT
    parentid
    , id
    , N'/' + localname + '[' + cast(idx as nvarchar(100)) + ']' AS xpath
    , text
  FROM #nodetree WHERE nodetype = 3
  UNION ALL
  SELECT
    parent.parentid
    , node.id
    , N'/' + localname + '[' + cast(idx as nvarchar(100)) + ']' + xpath
    , node.text
  FROM cte AS node
  INNER JOIN #nodetree parent on parent.id = node.parentid
)
SELECT
  REPLACE(xpath, '/text()[1]', '') as xpath, text
  FROM cte
  WHERE parentid IS NULL

Finally check the XPath built:

select 
  node.value(N'insert here one xpath row from the CTE', nvarchar(1000))
  from @xml.nodes(N'.') as T(node)