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>
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')