T-SQL and XML 

 1 --以默认的<row></row>包裹一个行对象，列名为属性名，值为行值
 2 SELECT TOP 1000 * FROM [AdventureWorks2008R2].[Person].[Person] FOR XML RAW
 3 
 4 --以默认的<row></row>包裹一个行对象，列名为元素名，包裹行值
 5 SELECT TOP 1000 * FROM [AdventureWorks2008R2].[Person].[Person] FOR XML RAW , ELEMENTS
 6 
 7 --以自定义<PersonDetails></PersonDetails>包裹一个行对象，列名为元素名，包裹行值
 8 SELECT TOP 1000 * FROM [AdventureWorks2008R2].[Person].[Person] FOR XML RAW('PersonDetails') , ELEMENTS
 9 
10 
11 --与FOR XML RAW 差不多，只是会自动以表名称包裹一个行对象，
12 SELECT TOP 1000 * FROM [Person].[Person] FOR XML AUTO
13 
14 --
15 CREATE TABLE xmltest
16 (
17     xid INT NOT NULL PRIMARY KEY,
18     xdoc XML NOT NULL
19 )
20 
21 SELECT * FROM dbo.xmltest
22 
23 INSERT  INTO dbo.xmltest
24 VALUES  ( 1, '
25          <states>
26             <state>
27                 <abbr>CA</abbr>
28                 <name>California</name>
29                 <city>Berkeley</city>
30                 <city>Los Angeles</city>    
31             </state>
32             <state>
33                 <abbr>DE</abbr>
34                 <name>Delaware</name>
35                 <city>Newark</city>
36                 <city>Wilmington</city>    
37             </state>
38          </states>
39          ' )
40 
41 
42 INSERT  INTO dbo.xmltest
43 VALUES  ( 2, '
44          <states>
45             <state>
46                 <abbr>CA</abbr>
47                 <name>California</name>
48                 <city>Berkeley</city>
49                 <city>Los Angeles</city>    
50             </state>
51             <state>
52                 <abbr>DE</abbr>
53                 <name>Delaware</name>
54                 <city>Newark</city>
55                 <city>Wilmington</city>    
56             </state>
57          </states>
58          ' )
59 
60 SELECT * FROM dbo.xmltest
61 
62 
63 
64

 

 1 DECLARE @xmlDoc XML;
 2 
 3 SET @xmlDoc = '<book id="0001">
 4                     <title>C Program</title>
 5                     <author>David</author>
 6                     <price>21</price>
 7                 </book>'
 8 
 9 
10 SELECT  @xmlDoc.query('/book/title')
11 
12 SELECT  @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')
13 
14 SELECT  @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')

 

posted @ 2014-11-01 12:40  海阔天空XM  阅读(155)  评论(0)    收藏  举报