SQL Select FOR XML into Solr document

[ipekcixve]

I'm trying to get a SQL select statement to generate XML which conforms to the Solr standard.Given a table like:\[code\]id | name--------- 1 | one 2 | two 3 | three\[/code\]I need a result which is like (with or without the root node):\[code\]<add> <doc> <field name="id">1</field> <field name="name">one</field> </doc> <doc> <field name="id">2</field> <field name="name">two</field> </doc> <doc> <field name="id">3</field> <field name="name">three</field> </doc></add>\[/code\]Is it possible to generate that structure using a \[code\]FOR XML\[/code\] query, or will I need an XSLT or some other mechanism to match that schema?