SQL Server操作XML(二)XML子句实例

 原始数据：

 （1）非元素Raw模式联合查询

SELECT P.Product_Name,P.ListPrice,S.Quantity
FROM Products P
JOIN Sales S
ON S.Product_ID=P.Product_ID
FOR XML RAW

 

 显示结果

 

<row Product_Name="Dell笔记本" ListPrice="3500.00" Quantity="100" />
<row Product_Name="Apple笔记本" ListPrice="5000.00" Quantity="220" />
<row Product_Name="HP笔记本" ListPrice="400.00" />

 

（2）非元素Auto模式联合查询

 

SELECT P.Product_Name,P.ListPrice,S.Quantity
FROM Products P
JOIN Sales S
ON S.Product_ID=P.Product_ID
FOR XML AUTO

 

 

显示结果

 

<P Product_Name="Dell笔记本" ListPrice="3500.00">
  <S Quantity="100" />
</P>
<P Product_Name="Apple笔记本" ListPrice="5000.00">
  <S Quantity="220" />
</P>
<P Product_Name="HP笔记本" ListPrice="400.00">
  <S />
</P>

 （3）元素Raw模式联合查询

SELECT P.Product_Name,P.ListPrice,S.Quantity
FROM Products P
JOIN Sales S
ON S.Product_ID=P.Product_ID
FOR XML RAW,elements

显示结果

<row>
  <Product_Name>Dell笔记本</Product_Name>
  <ListPrice>3500.00</ListPrice>
  <Quantity>100</Quantity>
</row>
<row>
  <Product_Name>Apple笔记本</Product_Name>
  <ListPrice>5000.00</ListPrice>
  <Quantity>220</Quantity>
</row>
<row>
  <Product_Name>HP笔记本</Product_Name>
  <ListPrice>400.00</ListPrice>
</row>

（4）元素Auto模式联合查询

 

SELECT P.Product_Name,P.ListPrice,S.Quantity
FROM Products P
JOIN Sales S
ON S.Product_ID=P.Product_ID
FOR XML AUTO,elements

显示结果

 

<P>
  <Product_Name>Dell笔记本</Product_Name>
  <ListPrice>3500.00</ListPrice>
  <S>
    <Quantity>100</Quantity>
  </S>
</P>
<P>
  <Product_Name>Apple笔记本</Product_Name>
  <ListPrice>5000.00</ListPrice>
  <S>
    <Quantity>220</Quantity>
  </S>
</P>
<P>
  <Product_Name>HP笔记本</Product_Name>
  <ListPrice>400.00</ListPrice>
  <S />
</P>

(5)空值支持

 

SELECT P.Product_Name,P.ListPrice,S.Quantity
FROM Products P
JOIN Sales S
ON S.Product_ID=P.Product_ID
FOR XML RAW,elements xsinil

显示结果：

 

 

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Product_Name>Dell笔记本</Product_Name>
  <ListPrice>3500.00</ListPrice>
  <Quantity>100</Quantity>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Product_Name>Apple笔记本</Product_Name>
  <ListPrice>5000.00</ListPrice>
  <Quantity>220</Quantity>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Product_Name>HP笔记本</Product_Name>
  <ListPrice>400.00</ListPrice>
  <Quantity xsi:nil="true" />
</row>

 （6）子查询，Type应用

 

 

SELECT Product_Name,ListPrice,
(SELECT Quantity
FROM Sales
WHERE Products.Product_ID=Sales.Product_ID
FOR XML RAW )
FROM Products
FOR XML AUTO

显示结果

 

 

不能将未命名的表用作 XML 标识符，也不能将未命名的列用于属性名称。请在 SELECT 语句中使用 AS 对未命名的列/表进行命名

 因为不指定的话，返回子查询是字符串，必须利用Type指定

 

 

SELECT Product_Name,ListPrice,
(SELECT Quantity
FROM Sales
WHERE Products.Product_ID=Sales.Product_ID
FOR XML RAW,TYPE)
FROM Products
FOR XML AUTO

显示结果：

 

<Products Product_Name="Dell笔记本" ListPrice="3500.00">
  <row Quantity="100" />
</Products>
<Products Product_Name="Apple笔记本" ListPrice="5000.00">
  <row Quantity="220" />
</Products>
<Products Product_Name="HP笔记本" ListPrice="400.00">
  <row />
  <row Quantity="300" />
</Products>

 （7）XML格式化

 

利用EXPLICIT显式格式化XML

 

select
1 as tag,
null as parent,
Product_Name  as [产品!1!名称!xml],
ListPrice  as [产品!1!价格!xml]
from Products
for xml EXPLICIT

显式结果：

 

<产品>
  <名称>Dell笔记本</名称>
  <价格>3500.00</价格>
</产品>
<产品>
  <名称>Apple笔记本</名称>
  <价格>5000.00</价格>
</产品>
<产品>
  <名称>HP笔记本</名称>
  <价格>400.00</价格>
</产品>

 

更为简单地，可以使用PATH代替

select
Product_Name  as "产品/名称",
ListPrice  as "产品/价格"
from Products
for xml  path

注意： AS后：  '@ID' 表示是一个Attribute

                      Name

                      详细/名称

                      详细/test()

 

 

 

 （8）如果生成XML文件，需要Root节点

 

select
Product_Name  as "产品/名称",
ListPrice  as "产品/价格"
from Products
for xml path ,root('root')

显示结果

 

<root>
  <row>
    <产品>
      <名称>Dell笔记本</名称>
      <价格>3500.00</价格>
    </产品>
  </row>
  <row>
    <产品>
      <名称>Apple笔记本</名称>
      <价格>5000.00</价格>
    </产品>
  </row>
  <row>
    <产品>
      <名称>HP笔记本</名称>
      <价格>400.00</价格>
    </产品>
  </row>
</root>