特别适用于SqlServer中的Xml

发布时间：2020-12-12 07:06:19 所属栏目：MsSql教程来源：网络整理

导读：我们有一个简单的表,需要转换为 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) NUL

我们有一个简单的表,需要转换为 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')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!