Special For Xml in SqlServer

for-xml, sql, sql-server-2008

Solution

The following XML PATH query should get you most of the way. You just need to add the stylesheet header.

SELECT 
'BusinessEntityID' AS 'document/field/@name'
,   BusinessEntityID AS 'document/field'
,   '' AS 'document'
,   'documentType' AS 'document/field/@name'
,   PersonType AS 'document/field'
,   '' AS 'document'
,   'Title' AS 'document/field/@name'
,   Title AS 'document/field'
,   '' AS 'document'
,   'FirstName' AS 'document/field/@name'
,   FirstName AS 'document/field'
,   '' AS 'document'
,   'MiddleName' AS 'document/field/@name'
,   MiddleName AS 'document/field'
,   '' AS 'document'
,   'LastName' AS 'document/field/@name'
,   LastName AS 'document/field'
,   '' AS 'document'
FROM @Person
FOR XML PATH(''), ROOT('documents')

Problem

we have a simple table and need convert to XML ``` Declare @Person TABLE ( [BusinessEntityID] [int] NOT NULL, [PersonType] [varchar](2) NULL, [Title] [varchar](30) NULL, [FirstName] [varchar](30) NULL, [MiddleName] [varchar](30) NULL, [LastName] [varchar](30) NULL) Insert Into @Person Values(10001,'IN','Article','Carolyn','Jo','Alonso') ``` we need this xml for output with For XML in sql server ``` <?xml-stylesheet type='text/xsl' href='result.xsl'?> <documents> <document> <field name="BusinessEntityID">10001</field> <field name="PersonType">IN</field> <field name="Title">Article</field> <field name="FirstName">Carolyn</field> <field name="MiddleName">Jo</field> <field name="LastName">Alonso</field> </document> </documents> ```

Original source