图书数据XML解析与SQL查询-优快云博客

本文介绍如何使用SQL查询语言结合XML数据进行图书信息的提取与操作，具体包括使用SQL语句从XML中获取图书的作者、标题、出版年份及价格等关键信息，并通过XML预处理和OPENXML函数实现复杂的数据结构处理。

DECLARE @Items XML
SET @Items =
     ' <bookstore>

<book category="COOKING">
  <title lang="en">Everyday Italian2</title>
  <author>Giada De Laurentiis</author>
  <year>2005</year>
  <price>30.00</price>
</book>

<book category="CHILDREN">
  <title lang="en">Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
</book>

<book category="WEB">
  <title lang="en">XQuery Kick Start</title>
  <author>James McGovern</author>
  <author>Per Bothner</author>
  <author>Kurt Cagle</author>
  <author>James Linn</author>
  <author>Vaidyanathan Nagarajan</author>
  <year>2003</year>
  <price>49.99</price>
</book>

<book category="WEB">
  <title lang="zh">Learning XML</title>
  <author>Erik T. Ray</author>
  <year>2003</year>
  <price>39.95</price>
</book>

</bookstore> '

DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @Items

SELECT * INTO #tt
FROM   OPENXML( @idoc, ' //book ', 1)
        WITH (
           MatchID NVARCHAR( 100) ' ./title/@lang ',
           MatchNum NVARCHAR( 100) ' ./year/text() ',
           UserID NVARCHAR( 32) ' ./price/text() ',
           Place NVARCHAR( 100) ' ./price/text() '
       )

-- SELECT Tab.Col.value('author[1]', 'varchar(max)') AS author,Tab.Col.value('title[1]', 'varchar(max)') AS title
--        --INTO #tt
-- FROM   @Items.nodes('//book') AS Tab(Col)

SELECT *
FROM   #tt
GO
DROP TABLE #tt

转载于:https://www.cnblogs.com/skydau/archive/2012/08/18/2645875.html