問題描述

如何寫一條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> 

這篇關于從 SQL Server 表以正確的語法生成 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！