xml实战：一把表转化为XML

--创建测试数据库

CREATE DATABASE TK431Chapter8

USE TK431Chapter8

--创建schema

declare @schema XML

SELECT @schema = c FROM OPENROWSET (

BULK 'C:/logRecordSchema.xsd', SINGLE_BLOB) AS TEMP(c)

CREATE XML SCHEMA COLLECTION LogRecordSchema AS @schema

--创建表

CREATE TABLE UniversalLog

( ID INT IDENTITY(1,1) NOT NULL,

LogDateTime DATETIME NOT NULL CONSTRAINT [DF_UniversalLog_LogDateTime]

DEFAULT (GetDate()),

ApplicationName NVARCHAR(50) NOT NULL,

LogRecord XML(LogRecordSchema) NULL )

--插入数据

INSERT UniversalLog(ApplicationName, LogRecord)

VALUES ( 'HR',

'<logRecord machine="server1" timestamp="2000-01-14T12:13:14Z">

<message>The user does not have enough permissions to execute query</message>

<module>DataAccessLayer</module>

</error>

</logRecord>')

INSERT UniversalLog(ApplicationName, LogRecord)

VALUES ('CustomerService',

'<logRecord machine="server2" timestamp="2000-01-15T12:13:14Z">

<message>User must change password on next login</message>

</information>

</logRecord>')

INSERT UniversalLog(ApplicationName, LogRecord)

VALUES ('HoursReport',

'<logRecord machine="server2" timestamp="2000-01-11T12:13:14Z">

<message>Hard Disk with ID #87230283 is not responding</message>

</information>

<message>Application can not start</message>

<module>AppLoader</module>

</error>

</logRecord>')

SELECT * FROM UniversalLog

--查询

USE AdventureWorks

--一FOR XML RAW

--每个字段作为属性

SELECT Department.[DepartmentID]

,History.[EmployeeID]

,History.[StartDate]

,Department.[Name]

,DATEDIFF(year, History.[StartDate], GetDate()) AS YearsToDate

FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History

WHERE Department.DepartmentID = History.DepartmentID

AND History.EndDate IS NULL

ORDER BY Department.[DepartmentID], History.[StartDate]

FOR XML RAW('OldestEmployeeByDepartment')

--每个字段作为子元素

SELECT Department.[DepartmentID]

,History.[EmployeeID]

,History.[StartDate]

,Department.[Name] AS DepartmentName

,DATEDIFF(year, History.[StartDate], GetDate()) AS YearsToDate

FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History

WHERE Department.DepartmentID = History.DepartmentID

AND History.EndDate IS NULL

ORDER BY Department.[DepartmentID], History.[StartDate]

FOR XML RAW('OldestEmployeeByDepartment'), ELEMENTS

--二FOR XML AUTO 模式（嵌套的XML格式）

SELECT Department.[DepartmentID]

,History.[EmployeeID]

,History.[StartDate]

,Department.[Name] AS DepartmentName

,DATEDIFF(year, History.[StartDate], GetDate()) AS YearsToDate

FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History

WHERE Department.DepartmentID = History.DepartmentID

AND History.EndDate IS NULL

ORDER BY Department.[DepartmentID], History.[StartDate] FOR XML AUTO

SELECT Department.[DepartmentID]

,History.[EmployeeID]

,History.[StartDate]

,Department.[Name] AS DepartmentName

,DATEDIFF(year, History.[StartDate], GetDate()) AS YearsToDate

FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History

WHERE Department.DepartmentID = History.DepartmentID

AND History.EndDate IS NULL

ORDER BY Department.[DepartmentID], History.[StartDate] FOR XML AUTO, ELEMENTS

把表转化为XML

--三FOR XML PATH 以元素为中心

USE AdventureWorks

SELECT History.[StartDate] '@StartDate'

,Department.[DepartmentID] 'Department/@id'

,Department.[Name] 'comment()'

,History.[EmployeeID] 'Department/Employee/@id'

,'Years in role:' 'Department/Employee/data()'

,DATEDIFF(year, History.[StartDate], GetDate()) 'Department/Employee/data()'

FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History

WHERE Department.DepartmentID = History.DepartmentID

AND History.EndDate IS NULL

ORDER BY Department.[DepartmentID], History.[StartDate] FOR XML PATH ('ForEachRow')

--如果希望针对NULL 值生成元素，则可以指定带有XSINIL 的ELEMENTS 指令

SELECT EmployeeID "@EmpID",

FirstName "EmpName/First",

MiddleName "EmpName/Middle",

LastName "EmpName/Last"

FROM HumanResources.Employee E, Person.Contact C

WHERE E.EmployeeID = C.ContactID

AND E.EmployeeID=1

FOR XML PATH, ELEMENTS XSINIL

SELECT

ProductModelID,

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH

SELECT ProductModelID,

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML RAW, ELEMENTS

-- 以上两种模式结果一样

--如果指定零长度字符串，则将不生成包装元素。

SELECT ProductModelID,

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH ('')

--生成的XML 将包含指定的行元素名称

SELECT ProductModelID,

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH ('ProductModel')

--在以下查询中，指定的ProductModelID 列名以@ 开头，且不包含斜杠标记(/)。因此，在生成的XML 中，将创建包含相应列值的<row> 元素的属性。

SELECT ProductModelID as "@id",

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH ('ProductModelData')

--指定root 选项来添加单个顶级元素

SELECT ProductModelID as "@id",

Name

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH ('ProductModelData'), root ('Root')

SELECT ProductModelID as "@id",

NAME AS "SomeChild/ModelName"

FROM Production.ProductModel

WHERE ProductModelID=122 or ProductModelID=119

FOR XML PATH ('ProductModelData'), root ('Root')

--Instructions 列是xml 类型，因此指定了xml 数据类型的query() 方法来检索该位置

SELECT ProductModelID as "@id",

Name,

Instructions.query('declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

/MI:root/MI:Location

') as ManuInstr

FROM Production.ProductModel

WHERE ProductModelID=7

FOR XML PATH ('ProductModelData'), root ('Root')

--使用WITH XMLNAMESPACES 绑定和在FOR XML 查询中使用前缀首先定义命名空间的前缀

WITH XMLNAMESPACES (

'uri1' as ns1,

'uri2' as ns2,

'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)

SELECT ProductModelID as "ns1:ProductModelID",

Name as "ns1:Name",

Instructions.query('

/MI:root/MI:Location

FROM Production.ProductModel

WHERE ProductModelID=7

FOR XML PATH ('ns2:ProductInfo'), root('ns1:root')

--使用PATH 模式生成值列表

SELECT ProductModelID as "@ProductModelID",

Name as "@ProductModelName",

(SELECT ProductID as "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')) as "@ProductIDs",

(SELECT Name as "ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')) as "ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 or ProductModelID=9

FOR XML PATH('ProductModelData')

--PATH 模式查询将构造带命名空间的XML

SELECT 'en' as "English/@xml:lang",

'food' as "English",

'ger' as "German/@xml:lang",

'Essen' as "German"

FOR XML PATH ('Translation')

-- 利用TYPE生产XML类型

DECLARE @myXML XML

SET @myXML = (SELECT 100 'col1',

200 'col2',

NULL 'col3',

400 'col4'

FOR XML RAW, ELEMENTS XSINIL, TYPE)

SELECT @myXML

--嵌套

SELECT Department.[DepartmentID],

Department.[Name],

(

SELECT EmployeeDepartmentHistory.[EmployeeID]

,EmployeeDepartmentHistory.[StartDate]

,DATEDIFF(year, EmployeeDepartmentHistory.[StartDate], GetDate()) AS

YearsToDate

FROM HumanResources.EmployeeDepartmentHistory

WHERE Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID

AND EmployeeDepartmentHistory.EndDate IS NULL

ORDER BY EmployeeDepartmentHistory.[StartDate]

FOR XML RAW('Employee'), TYPE

) AS Employees

FROM HumanResources.Department

ORDER BY Department.[DepartmentID]

FOR XML RAW('Department'), ELEMENTS, ROOT ('OldestEmployeeByDepartment')

--四FOR XML EXPLICIT模式太复杂可以用FOR XML PATH代替

posted @ 2009-02-17 09:57 深潭阅读(337) 评论(0) 编辑收藏举报

刷新页面返回顶部

深潭的网上家园

努力追逐自己的梦想！

xml实战：一把表转化为XML

公告

深潭的网上家园

努力追逐自己的梦想！

xml实战：一 把表转化为XML

公告

xml实战：一把表转化为XML