Hi Jacob,
Here is another approach to get same result. This approach uses the edge table from OPENXML function.
USE tempdb;
GO
CREATE TABLE #T (
id bigint NOT NULL PRIMARY KEY,
parentid bigint NULL,
nodetype int NOT NULL, -- 1 - element, 2 - attribute, 3 - text
localname nvarchar(max) NULL,
--prefix nvarchar(max) NULL,
--namespaceuri nvarchar(max) NULL,
--datatype nvarchar(128) NULL,
--prev bigint NULL,
[text] nvarchar(max)
);
DECLARE @idoc int
DECLARE @x XML
SET @x = '
my book
Myself
your book
you
The Art of XSD
A good friend
';
EXEC spxmlpreparedocument @idoc OUTPUT, @x;
INSERT INTO #T (
id,
parentid,
nodetype,
localname,
-- prefix,
-- namespaceuri,
-- datatype,
-- prev,
[text]
)
SELECT
id,
parentid,
nodetype,
localname,
-- prefix,
-- namespaceuri,
-- datatype,
-- prev,
[text]
FROM
OPENXML(@idoc, '.') AS T;
EXEC spxmlremovedocument @idoc;
WITH rs AS (
SELECT
id,
parentid,
nodetype,
localname AS FullPath,
[text] AS [Value],
CAST(id AS varbinary(64)) AS sort
FROM
#T
WHERE
parentid IS NULL
UNION ALL
SELECT
C.id,
C.parentid,
C.nodetype,
P.FullPath + CASE C.nodetype WHEN 1 THEN '/' WHEN 2 THEN '@' ELSE '' END + CASE WHEN C.nodetype = 3 THEN '' ELSE C.localname END,
C.[text] AS [Value],
CAST(P.sort + CAST(C.id AS binary(4)) AS varbinary(64))
FROM
rs AS P
INNER JOIN
#T AS C
ON P.id = C.parentid
)
SELECT
FullPath,
[Value]
FROM
rs
WHERE
nodetype = 3
ORDER BY
sort;
GO
DROP TABLE #T;
GO
Cheers,
AMB