parsing de XML avec TSQL – value () avec un problème de nœuds nesteds

J'parsing le file xml avec TSQL pour build une table pour une parsing plus approfondie. En utilisant de superbes conseils de xquery-lab-61-writing-a-récursive-cte-to-process-an-xml-document j'utilise CTE mais n'obtient pas le résultat désiré. Le problème est avec la fonction value () pour les nœuds avec childrens.

j'ai

DECLARE @x XML SELECT @x = ' <books> <book id="101"> <title>my book</title> <author>Myself</author> </book> <book id="202"> text before <title>your book</title> in the middle <author>you</author> text after </book> </books>' ;WITH cte AS ( SELECT 1 AS lvl, x.value('local-name(.)','VARCHAR(MAX)') AS FullPath, x.value('text()[1]','VARCHAR(MAX)') AS Value, x.query('.') AS CurrentNode, CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort FROM @x.nodes('/*') a(x) UNION ALL SELECT p.lvl + 1 AS lvl, CAST( p.FullPath + '/' + c.value('local-name(.)','VARCHAR(MAX)') AS VARCHAR(MAX) ) AS FullPath, CAST( c.value('text()[1]','VARCHAR(MAX)') AS VARCHAR(MAX) ) AS Value, c.query('.') AS CurrentNode, CAST( p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4) ) AS VARBINARY(MAX) ) AS Sort FROM cte p CROSS APPLY CurrentNode.nodes('/*/*') b(c) ), cte2 AS ( SELECT FullPath, Value, Sort FROM cte UNION ALL SELECT p.FullPath + '/@' + x.value('local-name(.)','VARCHAR(MAX)'), x.value('.','VARCHAR(MAX)'), Sort FROM cte p CROSS APPLY CurrentNode.nodes('/*/@*') a(x) ) SELECT FullPath, value FROM cte2 WHERE Value IS NOT NULL ORDER BY Sort

ce qui entraîne

 FullPath Value -------------------- ------------------------------ books\book\@id 101 books\book\title my book books\book\author Myself books\book text before books\book\@id 202 books\book\title your book books\book\author you

et j'ai besoin de quelque chose comme:

 FullPath Value -------------------- ------------------------------ books\book\@id 101 books\book\title my book books\book\author Myself books\book text before books\book\@id 202 books\book\title your book books\book in the middle books\book\author you books\book text after

Je préférerais find une solution en utilisant TSQL si possible. Je serai extrêmement reconnaissant pour toutes les bonnes solutions / conseils.

Cela est plus facile avec OPENXML au lieu d'utiliser le type de données XML.

Avec OPENXML, vous pouvez créer une table de périphérie contenant une ligne pour chaque noeud du file XML.

 declare @idoc int; exec sp_xml_preparedocument @idoc out, @x; select * from openxml(@idoc, '') exec sp_xml_removedocument @idoc;

Résultat:

 id parentid nodetype localname prefix namespaceuri datatype prev text --- ----------------- --------- ------ ------------ -------- ---- ------------- 0 NULL 1 books NULL NULL NULL NULL NULL 2 0 1 book NULL NULL NULL NULL NULL 3 2 2 id NULL NULL NULL NULL NULL 13 3 3 #text NULL NULL NULL NULL 101 4 2 1 title NULL NULL NULL NULL NULL 14 4 3 #text NULL NULL NULL NULL my book 5 2 1 author NULL NULL NULL 4 NULL 15 5 3 #text NULL NULL NULL NULL Myself 6 0 1 book NULL NULL NULL 2 NULL 7 6 2 id NULL NULL NULL NULL NULL 16 7 3 #text NULL NULL NULL NULL 202 8 6 3 #text NULL NULL NULL NULL text before 9 6 1 title NULL NULL NULL 8 NULL 17 9 3 #text NULL NULL NULL NULL your book 10 6 3 #text NULL NULL NULL 9 in the middle 11 6 1 author NULL NULL NULL 10 NULL 18 11 3 #text NULL NULL NULL NULL you 12 6 3 #text NULL NULL NULL 11 text after

Stockez la table de bord dans une table temporaire et effectuez un CTE récursif en utilisant id et parentid . Utilisez nodetype lorsque vous générez la colonne FullPath .

 declare @x xml; select @x = ' <books> <book id="101"> <title>my book</title> <author>Myself</author> </book> <book id="202"> text before <title>your book</title> in the middle <author>you</author> text after </book> </books>'; declare @idoc int; exec sp_xml_preparedocument @idoc out, @x; select * into #T from openxml(@idoc, ''); exec sp_xml_removedocument @idoc; with C as ( select T.id, T.parentid, T.localname as FullPath, T.text as Value from #T as T where T.parentid is null union all select T.id, T.parentid, C.FullPath + case T.nodetype when 1 then N'\' + T.localname -- Element node when 2 then N'\@' + T.localname -- Atsortingbute node when 3 then N'' -- Text node when 4 then N'' -- CDATA secotion node when 5 then N'' -- Entity reference node when 6 then N'' -- Entity node when 7 then N'' -- Processing instrution node when 8 then N'' -- Comment node when 9 then N'' -- Document node when 10 then N'' -- Document type node when 11 then N'' -- Document fragment node when 12 then N'' -- Notation node end, T.text from C inner join #T as T on C.id = T.parentid ) select C.FullPath, C.Value from C where C.Value is not null order by C.parentid, C.id; drop table #T;

Résultat:

 FullPath Value ------------------ -------------- books\book\@id 101 books\book\title my book books\book\author Myself books\book text before books\book in the middle books\book text after books\book\@id 202 books\book\title your book books\book\author you

SQL Fiddle