从 SQL Server 表以正确的语法生成 XML

2021-10-01 00:00:00 xml sql-server

如何写一条SQL语句生成这样的XML

How to write a SQL statement to generate XML like this

<ROOT>
  <Production.Product> 
     <ProductID>1 </ProductID>
     <Name>Adjustable Race</Name>
     ........
  </Production.Product>
</ROOT>

目前我正在使用

SELECT * FROM Production.Product
    FOR XML auto

结果是:

<ROOT>
   <Production.Product ProductID="1" Name="Adjustable Race" 
       ProductNumber="AR-5381" MakeFlag="0" FinishedGoodsFlag="0" 
       SafetyStockLevel="1000" ReorderPoint="750" StandardCost="0.0000" 
       ListPrice="0.0000" DaysToManufacture="0" SellStartDate="1998-06-01T00:00:00" 
       rowguid="694215B7-08F7-4C0D-ACB1-D734BA44C0C8" 
       ModifiedDate="2004-03-11T10:01:36.827" />

推荐答案

一种简单的方法是使用:

One simple way would be to use:

SELECT * 
FROM Production.Product
FOR XML AUTO, ELEMENTS

然后，您的数据应存储在 节点内的 XML 元素中.

Then, your data should be stored in XML elements inside the <Production.Product> node.

如果您需要更多控制，那么您应该查看 FOR XML PATH 语法 - 查看关于 SQL Server 2005 中 FOR XML 的新增功能 解释了 FOR XML PATH (以及其他新功能).

If you need even more control, then you should look at the FOR XML PATH syntax - check out this MSDN article on What's new in FOR XML in SQL Server 2005 which explains the FOR XML PATH (among other new features).

基本上，使用 FOR XML PATH，您可以非常轻松地控制事物的呈现方式 - 作为元素或作为属性 - 类似于:

Basically, with FOR XML PATH, you can control very easily how things are rendered - as elements or as attributes - something like:

SELECT 
    ProductID AS '@ProductID',  -- rendered as attribute on XML node
    Name, ProductNumber,        -- all rendered as elements inside XML node
    .....
FROM Production.Product
FOR XML PATH('NewProductNode')   -- define a new name for the XML node

这会给你类似的东西:

<NewProductNode ProductID="1">
   <Name>Adjustabel Race</Name>
   <ProductNumber>AR-5381</ProductNumber>
   .....
</NewProductNode>

相关文章