我们有一个简单的表,需要转换为
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')
我们需要这个xml用于在sql server中使用For XML输出
<?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>
解决方法
以下XML PATH查询应该可以帮助您完成大部分工作.您只需添加样式表标题即可.
SELECT
'BusinessEntityID' AS 'document/field/@name',BusinessEntityID AS 'document/field','' AS 'document','documentType' AS 'document/field/@name',PersonType AS 'document/field','Title' AS 'document/field/@name',Title AS 'document/field','FirstName' AS 'document/field/@name',FirstName AS 'document/field','MiddleName' AS 'document/field/@name',MiddleName AS 'document/field','LastName' AS 'document/field/@name',LastName AS 'document/field','' AS 'document'
FROM @Person
FOR XML PATH(''),ROOT('documents')
