Table Sql Server en XML avec plusieurs nœuds enfants

J'ai le tableau suivant

name | age | misc ------------------ david | 20 | foo john | 30 | bar

Et je veux le transformer en XML suivant:

 <doc> <field name="name" val="david" /> <field name="age" val="20" /> <field name="misc" val="foo" /> </doc> <doc> <field name="name" val="john" /> <field name="age" val="30" /> <field name="misc" val="bar" /> </doc>

J'ai pour ceci fonctionnant ci-dessous pour une colonne simple, cependant si j'essaye d'append une deuxième colonne pour un autre noeud de field j'obtiens l'erreur:

 Msg 9303, Level 16, State 1, Line 25 XQuery [query()]: Syntax error near 'name', expected '}'.

Ceci est un exemple de ce que j'essaie de faire et est prêt à être exécuté dans SQL Server Management Studio. Je ne trouve pas beaucoup de documentation sur la syntaxe et je suis complètement perdu pour des idées.

Toute aide est appréciée!

 declare @MyData table (name varchar(200), age varchar(200), misc varchar(200)) insert into @MyData values('david', '20', 'foo') insert into @MyData values('john', '30', 'bar') /*This one works fine*/ SELECT (select * from @MyData as MyData for xml auto, type).query ( ' for $d in /MyData return <doc>{ <field name="name" val="{data($d/@name)}" /> }</doc>' ) /*This one is what I want*/ SELECT (select * from @MyData as MyData for xml auto, type).query ( ' for $d in /MyData return <doc>{ <field name="name" val="{data($d/@name)}" /> <field name="age" val="{data($d/@age)}" /> <field name="misc" val="{data($d/@misc)}" /> }</doc>' )

Que dis-tu de ça..

 select (select 'name' as 'field/@name', a.name as 'field/@val' for xml path(''), type), (select 'age' as 'field/@name', a.age as 'field/@val' for xml path(''), type), (select 'misc' as 'field/@name', a.misc as 'field/@val' for xml path(''), type) from MyData a for xml path('doc')

pour votre version de XQuery, essayez ceci: (Je viens de retirer les accolades) Est-ce que c'est OK?

 SELECT (select * from @MyData as MyData for xml auto, type).query ( ' for $d in /MyData return <doc> <field name="name" val="{data($d/@name)}" /> <field name="age" val="{data($d/@age)}" /> <field name="misc" val="{data($d/@misc)}" /> </doc>' )

Essentiellement, vous essayez d'get du XML pour datatables non pivotées. Commencez donc par get un identifiant de ligne unique qui n'est pas un champ (j'utiliserai un CTE et un numéro de ligne). De là, vous pouvez utiliser UNPIVOT et FOR XML EXPLICIT :

 ;with data as ( select name, age, misc, row_number() over(order by name) as 'row' from @MyData ) select 1 as tag, null as parent, row as [doc!1!row!hide], null as [field!2!name], null as [field!2!val] from data UNION select 2 as tag, 1 as parent, row as [doc!1!row!hide], fieldName as [field!2!name], val as [field!2!val] from data d UNPIVOT(val for fieldName in (name, age, misc)) up order by row, tag FOR XML EXPLICIT, ROOT('root')

le ROOT('root') est d'append un élément racine simple et est en plus du format xml demandé, mais j'ai pensé que cela pourrait être utile.

METTRE À JOUR
Après avoir examiné de plus près les plans d'exécution des requêtes, il vaudrait peut-être mieux simplement créer du text formaté puis lancer vers xml:

 select cast('<field name="name" val="'+name+'" />'+ '<field name="age" val="'+age+'" />'+ '<field name="misc" val="'+misc+'" />' as xml) from @MyData for xml path('doc')