MSSQL妙用：用XML解析精彩世界-猿码集

1. MSSQL中的XML数据类型

MSSQL中有一个十分强大的数据类型——XML。它可以存储和操作XML数据。如果你需要从一个扁平的表中读取、保存具有层次结构的数据，或是将数据导出到XML文件中，那么XML数据类型将是你的首选。同时，XML数据类型还可以与其他数据类型进行转换，例如varchar、nvarchar、int等。

1.1 XML数据类型的定义

XML数据类型是一种数据库数据类型，用于存储XML文档。使用XML数据类型时，可以将一个XML文档嵌入到数据库字段中。在MSSQL中，XML数据类型由XML文档的内容加上一个文档类型定义（DTD）或模式（XSD）组成，可以存储在一个单独的XML列中，也可以作为表的一个列。下面是XML数据类型的定义方式：


CREATE TABLE example (
   id INT PRIMARY KEY,
   xmlData XML
)

1.2 XML数据类型的操作

XML数据类型是一种强大的数据类型，MSSQL提供了很多函数和操作符，可以用来处理XML数据类型。这些函数和操作符可以用于从XML文档中提取数据、创建XML文档、修改XML文档等。

以下是一些XML数据类型的常用操作：

query()：从一个XML文档中提取数据。

value()：从一个XML文档中提取一个具体的值。

nodes()：从一个XML文档中提取指定的节点或节点集合。

exist()：查找一个XML文档是否包含指定的节点。

Xquery表达式：用于在XML文档中查找和选择节点。

2. 基本的XML解析操作

如果你有至少一定的XML的基础，我们可以使用MSSQL对XML进行解析。以下是一个简单的XML文档：


<Books>
   <Book ISBN="ISBN-13:978-0596513">
      <Title>Learning XML, Second Edition</Title>
      <Author>Erik T. Ray</Author>
      <Date>November 2003</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
   <Book ISBN="ISBN-13:978-0596100088">
      <Title>XSLT, 2nd Edition</Title>
      <Author>Doug Tidwell</Author>
      <Date>September 2008</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
</Books>

我们将使用query()函数和Xquery表达式来解析它：


DECLARE @xmlData XML
SET @xmlData = '<Books>
   <Book ISBN="ISBN-13:978-0596513">
      <Title>Learning XML, Second Edition</Title>
      <Author>Erik T. Ray</Author>
      <Date>November 2003</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
   <Book ISBN="ISBN-13:978-0596100088">
      <Title>XSLT, 2nd Edition</Title>
      <Author>Doug Tidwell</Author>
      <Date>September 2008</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
</Books>'
SELECT @xmlData.query('/Books/Book') AS BookList

上面的代码使用query()函数和Xquery表达式提取了XML文档中所有的Book节点，并将它们作为一个节点列表返回。

现在，我们来提取所有的书本信息：


SELECT 
  data.value('ISBN[1]', 'varchar(50)') AS ISBN,
  data.value('Title[1]', 'varchar(100)') AS Title,
  data.value('Author[1]', 'varchar(100)') AS Author,
  data.value('Date[1]', 'varchar(100)') AS PublishDate,
  data.value('Publisher[1]', 'varchar(100)') AS Publisher
FROM @xmlData.nodes('/Books/Book') AS books(data)

通过上面的代码，我们将所有的ISBN、Title、Author、PublishDate和Publisher的数据提取出来，并输出到结果集中。

3. 在SQL Server中生成XML

现在，我们来看看如何在SQL Server中生成XML。以下是一些示例代码，演示了如何使用FOR XML语句生成XML。

3.1 生成带有属性的XML

以下示例展示了如何使用FOR XML语句生成一个带有属性的XML：


SELECT 'Learning XML, Second Edition' AS Title, 'Erik T. Ray' AS Author
FOR XML RAW('Book'), ROOT('BookList')

在上面的代码中，我们使用了FOR XML语句和RAW模式，将查询结果作为一个<Book>节点返回，并将所有的<Book>节点封装在一个<BookList>节点中。

3.2 生成带有子节点的XML

以下示例展示了如何使用FOR XML语句生成一个带有子节点的XML：


SELECT 'Learning XML, Second Edition' AS [Title],
       'Erik T. Ray' AS [Author],
       'November 2003' AS [Date],
       'O''Reilly Media' AS [Publisher]
FOR XML PATH('book'), ROOT('BookList')

在上面的代码中，我们使用了FOR XML语句和PATH模式，将查询结果作为一个<book>节点返回，并将所有的<book>节点封装在一个<BookList>节点中。在这个例子中，使用了SQL Server的XML escape符号（&、<、>、'和"）。

4. 使用OPENXML解析XML

上面的示例展示了如何使用query()函数和FOR XML语句从一个XML文档中提取数据，并使用FOR XML语句生成XML文档。除此之外，还有另外一种方法可以解析XML文档——使用OPENXML函数。如果你需要从外部的XML文档中读取数据，那么使用OPENXML函数会更加方便。

4.1 使用OPENXML解析XML文档

以下是一个使用OPENXML函数解析XML文档的示例。假设你有以下的XML文件：


<Books>
   <Book ISBN="ISBN-13:978-0596513">
      <Title>Learning XML, Second Edition</Title>
      <Author>Erik T. Ray</Author>
      <Date>November 2003</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
   <Book ISBN="ISBN-13:978-0596100088">
      <Title>XSLT, 2nd Edition</Title>
      <Author>Doug Tidwell</Author>
      <Date>September 2008</Date>
      <Publisher>O'Reilly Media</Publisher>
   </Book>
</Books>

使用OPENXML函数可以将这个XML文件转换成一个MSSQL表模式（即将XML文件中的数据映射到数据库中的行和列）。


DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc = '<Books>
   <Book ISBN="ISBN-13:978-0596513">
      <Title>Learning XML, Second Edition</Title>
      <Author>Erik T. Ray</Author>
      <Date>November 2003</Date>
      <Publisher>O''Reilly Media</Publisher>
   </Book>
   <Book ISBN="ISBN-13:978-0596100088">
      <Title>XSLT, 2nd Edition</Title>
      <Author>Doug Tidwell</Author>
      <Date>September 2008</Date>
      <Publisher>O''Reilly Media</Publisher>
   </Book>
</Books>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML(@idoc, '/Books/Book', 2)
  WITH (ISBN VARCHAR(20) '@ISBN',
        Title VARCHAR(100) 'Title',
        Author VARCHAR(100) 'Author',
        Date VARCHAR(100) 'Date',
        Publisher VARCHAR(100) 'Publisher')
        
EXEC sp_xml_removedocument @idoc

在上面的代码中，使用sp_xml_preparedocument存储XML文档的引用，并将引用存储在idoc变量中。然后，使用OPENXML函数从XML文档中读取数据，并将结果集作为MSSQL表返回。最后，使用sp_xml_removedocument删除已保存的XML文档引用。

5. 结论

MSSQL在XML处理方面提供了很多强大的工具和函数，这使得我们可以轻松地将XML数据导入到数据库中、从数据库中读取并解析XML数据。这为企业应用程序的持久性和数据交换提供了便利。

MSSQL妙用：用XML解析精彩世界