用sql语句返回xml格式

Question

------------------------------------
-- Author:  happyflsytone  
-- Date:2008-10-20 11:07:29
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(ID INT,speed INT,time NVARCHAR(5))
Go
INSERT INTO ta
SELECT 1,10,'08：00' UNION ALL
SELECT 1,20,'08：05' UNION ALL
SELECT 1,30,'08：15' UNION ALL
SELECT 2,10,'08：01' UNION ALL
SELECT 2,20,'08：14' UNION ALL
SELECT 3,15,'08：12' 
GO
--Start
SELECT [vehicle].id ,[data].speed,[data].time
FROM ta as [data]
left join (select distinct id from ta) as [vehicle]
on [data].id = [vehicle].id 
for xml auto,elements
--Result:
/*
<vehicle>
  <id>1</id>
  <data>
    <speed>10</speed>
    <time>08：00</time>
  </data>
  <data>
    <speed>20</speed>
    <time>08：05</time>
  </data>
  <data>
    <speed>30</speed>
    <time>08：15</time>
  </data>
</vehicle>
<vehicle>
  <id>2</id>
  <data>
    <speed>10</speed>
    <time>08：01</time>
  </data>
  <data>
    <speed>20</speed>
    <time>08：14</time>
  </data>
</vehicle>
<vehicle>
  <id>3</id>
  <data>
    <speed>15</speed>
    <time>08：12</time>
  </data>
</vehicle>*/
--End 

Answer 1

SQL SERVER 2005 XML 增强
USE AdventureWorks
GoSELECT ProductID,Name, ListPrice FROM Production.Product Product
 --包含XML Schema(XSD)
SELECT ProductID,Name, ListPrice FROM Production.Product Product
FOR XML AUTO,ELEMENTS XSINIL, XMLSCHEMA 
--通过Type 内层的查询结果会以子元素的方式嵌在外层的元素内
SELECT ProductID, Name, ListPrice, 
(SELECT ReviewerName,Comments FROM Production.ProductReview ProductReview
WHERE ProductReview.ProductID=Product.ProductID
FOR XML AUTO, Elements, TYPE) AS Description
FROM Production.Product Product
 --子查询变成外层查询的子元素
SELECT ProductID, Name, ListPrice, 
(SELECT ReviewerName,Comments FROM Production.ProductReview ProductReview
WHERE ProductReview.ProductID=Product.ProductID
FOR XML AUTO, Elements, TYPE)
FROM Production.Product Product
FOR XML AUTO
 
--以XPath 描述输出XML 文件的结构
SELECT ProductID AS "@ProductID",
Name AS "*",
Size AS "Description/@Size",
Color AS "Description/text()"
FROM Production.Product
FOR XML PATH, ROOT(N'产品')
 
XML 类型
USE AdventureWorks
Go-- 创建存放untyped XML 字段的数据表
CREATE TABLE Sales.DeliveryReport
(DeliveryReportID int IDENTITY PRIMARY KEY,
 Comments xml
 )-- 插入untyped XML
INSERT INTO Sales.DeliveryReport
VALUES
('<Comment>Address not found</Comment>')-- 虽然是untyped，XML 数据依然要是Well formed
-- 所以以下的数据会发生错误
INSERT INTO Sales.DeliveryReport
VALUES
('<Comment>Address not found')
GO-- 在数据类型间转换
DECLARE @xml XML,@str NVARCHAR(2000)
SET @str=N'<项目>
<细节Id="1">文字</细节>
<细节Id="2">文字</细节>
   </项目>'-- 隐含转换(implicit cast)
SET @xml=@str
INSERT INTO Sales.DeliveryReport
VALUES
(@xml)-- 明显转换(explicit cast)
DECLARE @xml XML,@str NVARCHAR(2000)
SET @str=N'<项目>
<细节Id="1">文字</细节>
<细节Id="2">文字</细节>
   </项目>'SET @xml=CAST(@str AS XML)
SET @xml=@str
INSERT INTO Sales.DeliveryReport
VALUES
(@xml)创建XML schema
USE TempDB
GO-- 创建XML schema collection
CREATE XML SCHEMA COLLECTION mySchemas
AS
N'<?xml version="1.0" ?>
<xs:schema xmlns="http://schemas.mySample.com/Consultants" 
           attributeFormDefault="unqualified" elementFormDefault="qualified" 
           targetNamespace="http://schemas.mySample.com/Consultants" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="ConsultantList">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Consultant">
          <xs:complexType>
            <xs:sequence>
  <xs:element minOccurs="1" name="ConsultantName" type="xs:string" />
              <xs:element maxOccurs="unbounded" name="Domain" type="xs:string" />
              <xs:element name="Email" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="ID" type="xs:int" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'--查阅已注册的XML Schema collection
SELECT * FROM sys.xml_schema_collections--查看一下上述Schema 包含了哪些元素
SELECT c.* FROM sys.xml_schema_components c
JOIN 
sys.xml_schema_collections s
ON c.xml_collection_id = s.xml_collection_id
WHERE s.name='mySchemas'-- 创建存储typed XML 的数据表
CREATE TABLE tblConsultant
(ID int IDENTITY PRIMARY KEY,
 Data xml(DOCUMENT mySchemas)
)--通过Schema 确认数据正确与否
-- 插入typed XML
INSERT tblConsultant VALUES(N'
<ConsultantList xmlns="http://schemas.mySample.com/Consultants">
  <Consultant ID="43659">
    <ConsultantName>姚巧玫</ConsultantName>
    <Domain>SQL</Domain>
<Email>[email protected]</Email>
  </Consultant>
</ConsultantList>
')--插入少掉Domain 的XML
INSERT tblConsultant VALUES(N'
<ConsultantList xmlns="http://schemas.mySample.com/Consultants">
  <Consultant ID="43659">
    <ConsultantName>姚巧玫</ConsultantName>
<Email>[email protected]</Email>
  </Consultant>
</ConsultantList>
')
XML查询
USE TempDB
GO
IF  EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[mySchemas]')
DROP XML SCHEMA COLLECTION  [dbo].[mySchemas]
GO
CREATE XML SCHEMA COLLECTION mySchemas
AS
N'<?xml version="1.0" ?>
<xs:schema xmlns="http://schemas.mySample.com/Consultants" 
           attributeFormDefault="unqualified" elementFormDefault="qualified" 
           targetNamespace="http://schemas.mySample.com/Consultants" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="ConsultantList">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Consultant">
          <xs:complexType>
            <xs:sequence>
  <xs:element minOccurs="1" name="ConsultantName" type="xs:string" />
              <xs:element maxOccurs="unbounded" name="Domain" type="xs:string" />
              <xs:element name="Email" type="xs:string" />
            </xs:sequence>
            <xs:attribute name="ID" type="xs:int" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'--创建存放符合上述XML Schema 定义的XML 数据类型字段数据表
CREATE TABLE myXML(c1 INT IDENTITY(1,1) PRIMARY KEY,
c2 XML(DOCUMENT mySchemas),c3 DateTime,c4 nvarchar(10))
GO--插入一笔符合条件的记录
INSERT myXML(c2,c3,c4) VALUES(N'
<ConsultantList xmlns="http://schemas.mySample.com/Consultants">
  <Consultant ID="43659">
    <ConsultantName>姚巧玫</ConsultantName>
    <Domain>SQL</Domain>
<Email>[email protected]</Email>
  </Consultant>
  <Consultant ID="43660">
    <ConsultantName>胡百敬</ConsultantName>
    <Domain>SQL</Domain>
    <Domain>.NET</Domain>
    <Email>[email protected]</Email>
  </Consultant>
</ConsultantList>
','2004/11/25',N'报表平台案')--XPath 练习
--因为插入数据有指定namespace，所以此处一定要用namespace 定义元素，否则会返回
--找不到元素的错误
SELECT c2.query('declare default element namespace "http://schemas.mySample.com/Consultants";
 /ConsultantList/Consultant') Consultants FROM myXML SELECT c2.query('declare default element namespace "http://schemas.mySample.com/Consultants";
 (/ConsultantList/Consultant)[1]') Consultants FROM myXML SELECT c2.value('declare default element namespace "http://schemas.mySample.com/Consultants";
 (/ConsultantList/Consultant/@ID)[1]','int') Consultants FROM myXML
 SELECT c2.query('declare default element namespace "http://schemas.mySample.com/Consultants";
 /ConsultantList/Consultant[@ID=43659]') Consultants FROM myXML

Answer 2

在SQL2005下这么写能达到楼主的要求：IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(ID INT,speed INT,time NVARCHAR(5))
Go
INSERT INTO ta
SELECT 1,10,'08：00' UNION ALL
SELECT 1,20,'08：05' UNION ALL
SELECT 1,30,'08：15' UNION ALL
SELECT 2,10,'08：01' UNION ALL
SELECT 2,20,'08：14' UNION ALL
SELECT 3,15,'08：12' 
GOSELECT ta.ID AS '@id'
,(SELECT time,speed FROM ta b WHERE b.ID = ta.ID FOR xml path('data'),type)
FROM ta
GROUP BY ID
FOR xml path('vehicle')--Result:
/*
<vehicle id="1">
  <data>
    <time>08:00</time>
    <speed>10</speed>
  </data>
  <data>
    <time>08:05</time>
    <speed>20</speed>
  </data>
  <data>
    <time>08:15</time>
    <speed>30</speed>
  </data>
</vehicle>
<vehicle id="2">
  <data>
    <time>08:01</time>
    <speed>10</speed>
  </data>
  <data>
    <time>08:14</time>
    <speed>20</speed>
  </data>
</vehicle>
<vehicle id="3">
  <data>
    <time>08:12</time>
    <speed>15</speed>
  </data>
</vehicle>
*/

Answer 3

  <?xml version="1.0" encoding="gb2312" ?> 
  <all>
  <result>0</result> 
  <datas>
  <vehicle busnum="2078">
  <data>
  <speed>9</speed> 
  <time>2008 08 31 08:00:09</time> 
  </data>
  <data>
  <speed>22</speed> 
  <time>2008 08 31 08:00:21</time> 
  </data>
  <data>
  <speed>0</speed> 
  <time>2008 08 31 08:00:41</time> 
  </data>
  <data>
  <speed>3</speed> 
  <time>2008 08 31 08:00:51</time> 
  </data>
  </vehicle>
  <vehicle busnum="0026">
  <data>
  <speed>44</speed> 
  <time>2008 08 31 08:00:02</time> 
  </data>
  <data>
  <speed>39</speed> 
  <time>2008 08 31 08:00:13</time> 
  </data>
  <data>
  <speed>46</speed> 
  <time>2008 08 31 08:00:23</time> 
  </data>
  <data>
  <speed>39</speed> 
  <time>2008 08 31 08:00:33</time> 
  </data>
  <data>
  <speed>20</speed> 
  <time>2008 08 31 08:00:43</time> 
  </data>
  <data>
  <speed>11</speed> 
  <time>2008 08 31 08:00:54</time> 
  </data>
  </vehicle>
  </datas>
  </all>
在3楼的结果基础上在套上几个标记，怎么搞？

Answer 4

<result>0 </result> 
怎么来的？我直接写死在SQL代码里了，你替换下SELECT 0 AS result,
(
SELECT ta.ID AS '@id'
,(SELECT time,speed FROM ta b WHERE b.ID = ta.ID FOR xml path('data'),type)
FROM ta
GROUP BY ID
FOR xml path('vehicle'),root('datas'),type
) 
FOR xml path('all')

Answer 5

sql2005用for xml path轻松得到。sql2000下参见
http://blog.csdn.net/fcuandy/archive/2007/08/20/1751334.aspx

Answer 6

如何将查询到的结果存到xml变量中
declare @s   xml@s='select * from table for xml auto'这样写不对，谁能告诉我怎么写

Answer 7

declare @s xml
set @s=(select * from table for xml auto)