DECLARE
@DOC XML
=
'
<books>
<book category="C#">
<title language="en">C# in Depth</title>
<author>John Skeet</author>
<year>2010</year>
<price>62.30</price>
</book>
<book category="C#">
<title language="cn">Effective C#</title>
<author>Bill Wagner</author>
<year>2010</year>
<price>49.00</price>
</book>
<book category="MSSQL">
<title language="cn">SQL2008 技术内幕</title>
<author>Itzik Ben-Gan</author>
<year>2010</year>
<price>90.20</price>
</book>
<book category="javascipt">
<title language="cn">JavaScript权威指南</title>
<author>David Flanagan</author>
<year>2007</year>
<price>87.20</price>
</book>
</books>
';
-- 查询所有书籍的分类
SELECT
T.C.value( ' @category ', ' VARCHAR(16) ')
FROM @DOC.nodes( ' /books/book ') AS T (C);
-- 查询所有C#书籍的名称,作者,价格,年份
WITH B AS
(
SELECT @DOC.query( ' //book[@category="C#"] ') AS BookNode
)
SELECT
T.C.value( ' title[1]/@language ', ' VARCHAR(32) ') AS [ language ],
T.C.value( ' title[1] ', ' VARCHAR(32) ') AS title,
T.C.value( ' author[1] ', ' VARCHAR(16) ') AS author,
T.C.value( ' year[1] ', ' INT ') AS [ year ],
T.C.value( ' price[1] ', ' DECIMAL(19,2) ') AS price
FROM B
CROSS APPLY B.BookNode.nodes( ' /book ') AS T (C);
-- 查询所有书籍的语言和名称
SELECT
T.C.value( ' @language[1] ', ' varchar(56) ') AS [ Language ],
T.C.value( ' . ', ' VARCHAR(56) ') AS TITLE
FROM @DOC.nodes( ' /books/book/title ') AS T (C);
<books>
<book category="C#">
<title language="en">C# in Depth</title>
<author>John Skeet</author>
<year>2010</year>
<price>62.30</price>
</book>
<book category="C#">
<title language="cn">Effective C#</title>
<author>Bill Wagner</author>
<year>2010</year>
<price>49.00</price>
</book>
<book category="MSSQL">
<title language="cn">SQL2008 技术内幕</title>
<author>Itzik Ben-Gan</author>
<year>2010</year>
<price>90.20</price>
</book>
<book category="javascipt">
<title language="cn">JavaScript权威指南</title>
<author>David Flanagan</author>
<year>2007</year>
<price>87.20</price>
</book>
</books>
';
-- 查询所有书籍的分类
SELECT
T.C.value( ' @category ', ' VARCHAR(16) ')
FROM @DOC.nodes( ' /books/book ') AS T (C);
-- 查询所有C#书籍的名称,作者,价格,年份
WITH B AS
(
SELECT @DOC.query( ' //book[@category="C#"] ') AS BookNode
)
SELECT
T.C.value( ' title[1]/@language ', ' VARCHAR(32) ') AS [ language ],
T.C.value( ' title[1] ', ' VARCHAR(32) ') AS title,
T.C.value( ' author[1] ', ' VARCHAR(16) ') AS author,
T.C.value( ' year[1] ', ' INT ') AS [ year ],
T.C.value( ' price[1] ', ' DECIMAL(19,2) ') AS price
FROM B
CROSS APPLY B.BookNode.nodes( ' /book ') AS T (C);
-- 查询所有书籍的语言和名称
SELECT
T.C.value( ' @language[1] ', ' varchar(56) ') AS [ Language ],
T.C.value( ' . ', ' VARCHAR(56) ') AS TITLE
FROM @DOC.nodes( ' /books/book/title ') AS T (C);