sql 和xml

一·常用基本功能

表结果：

1.

<row>
  <ID>1035</ID>
  <Rate>6.12</Rate>
  <Date>2013-11-28</Date>
  <Remark>正常</Remark>
</row>
<row>
  <ID>1036</ID>
  <Rate>6.15</Rate>
  <Date>2013-11-28</Date>
  <Remark>正常</Remark>
</row>

SELECT * 
FROM dbo.T_H_ExchangeRate
FOR XML PATH

2.

<ID>1035</ID>
<Rate>6.12</Rate>
<Date>2013-11-28</Date>
<Remark>正常</Remark>
<ID>1036</ID>
<Rate>6.15</Rate>
<Date>2013-11-28</Date>
<Remark>正常</Remark>

SELECT * 
FROM dbo.T_H_ExchangeRate
FOR XML PATH('')

3.

<SUN>
  <ID>1035</ID>
  <Rate>6.12</Rate>
  <Date>2013-11-28</Date>
  <Remark>正常</Remark>
</SUN>
<SUN>
  <ID>1036</ID>
  <Rate>6.15</Rate>
  <Date>2013-11-28</Date>
  <Remark>正常</Remark>
</SUN>

SELECT * 
FROM dbo.T_H_ExchangeRate
FOR XML PATH('SUN')

4.

<汉语>
  <序号>1035</序号>
  <汇率>6.12</汇率>
  <日期>2013-11-28</日期>
  <标记>正常</标记>
</汉语>
<汉语>
  <序号>1036</序号>
  <汇率>6.15</汇率>
  <日期>2013-11-28</日期>
  <标记>正常</标记>
</汉语>

SELECT ID AS '序号',
       Rate AS '汇率',
       Date AS '日期',
       Remark AS '标记'
FROM dbo.T_H_ExchangeRate
FOR XML PATH('汉语')

5.

--生成带顶层节点
<Table>
  <Row>
    <ID>1035</ID>
    <Rate>6.12</Rate>
    <Date>2013-11-28</Date>
    <Remark>正常</Remark>
  </Row>
  <Row>
    <ID>1036</ID>
    <Rate>6.15</Rate>
    <Date>2013-11-28</Date>
    <Remark>正常</Remark>
  </Row>
</Table>

SELECT *
FROM dbo.T_H_ExchangeRate
FOR XML PATH('Row'),ROOT('Table')

--还原成DataTable的形式，查找节点
SELECT T.x.value('(ID)[1]','nvarchar(50)') AS ID,
       T.x.value('(Rate)[1]','nvarchar(50)') AS Rate,
       T.x.value('(Date)[1]','nvarchar(50)') AS Date,
       T.x.value('(Remark)[1]','nvarchar(50)') AS Remark
FROM @xmlStr.nodes('/Table/Row') T(x)

6.

[正常],[正常],

SELECT '['+Remark+'],'
FROM dbo.T_H_ExchangeRate
FOR XML PATH('')

7.

数据源：

查询结果：

--源代码
SELECT C.O_USER, LEFT(C.Operate,LEN(Operate)-1) Operate
FROM (
SELECT A.O_USER,
       (
          SELECT B.O_TYPE+','
          FROM dbo.T_UP_Olog AS B
          WHERE B.O_USER = A.O_USER
          FOR XML PATH('')
       ) Operate
FROM dbo.T_UP_Olog A
GROUP BY A.O_USER
)AS C