当前位置：Gxlcms > 数据库问题 > XML 在SQLServer中的使用

XML 在SQLServer中的使用

时间：2021-07-01 10:21:17 帮助过：4人阅读

MSDN XQuery language reference.

那我们现在先来通过例子来看一下query()和value 两个方法是如何使用XML数据的。需要注意的是我接下来的测试环境是SQLServer2008 R2。实例中包含了ClientDB 数据库、ClientInfoCollection 的XML数据以及ClientInfo 表。

  1. USE master;
  2. GO
  3. IF DB_ID(‘ClientDB‘) IS NOT NULL
  4. DROP DATABASE ClientDB;
  5. GO
  6. CREATE DATABASE ClientDB;
  7. GO
  8. USE ClientDB;
  9. GO
  10. IF OBJECT_ID(‘ClientInfoCollection‘) IS NOT NULL
  11. DROP XML SCHEMA COLLECTION ClientInfoCollection;
  12. GO
  13. CREATE XML SCHEMA COLLECTION ClientInfoCollection AS 
  14. ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  15. xmlns="urn:ClientInfoNamespace" 
  16. targetNamespace="urn:ClientInfoNamespace" 
  17. elementFormDefault="qualified">
  18.   <xsd:element name="People">
  19.     <xsd:complexType>
  20.       <xsd:sequence>
  21.         <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
  22.           <xsd:complexType>
  23.             <xsd:sequence>
  24.               <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
  25.               <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
  26.               <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" />
  27.             </xsd:sequence>
  28.             <xsd:attribute name="id" type="xsd:integer" use="required"/>
  29.           </xsd:complexType>
  30.         </xsd:element>
  31.       </xsd:sequence>
  32.     </xsd:complexType>
  33.   </xsd:element>
  34. </xsd:schema>‘;
  35. GO
  36. IF OBJECT_ID(‘ClientInfo‘) IS NOT NULL
  37. DROP TABLE ClientInfo;
  38. GO
  39. CREATE TABLE ClientInfo
  40. (
  41.   ClientID INT PRIMARY KEY IDENTITY,
  42.   Info_untyped XML,
  43.   Info_typed XML(ClientInfoCollection)
  44. );
  45. INSERT INTO ClientInfo (Info_untyped, Info_typed)
  46. VALUES
  47. (
  48.   ‘<?xml version="1.0" encoding="UTF-8"?>
  49.   <People>
  50.     <Person id="1234">
  51.       <FirstName>John</FirstName>
  52.       <LastName>Doe</LastName>
  53.     </Person>
  54.     <Person id="5678">
  55.       <FirstName>Jane</FirstName>
  56.       <LastName>Doe</LastName>
  57.     </Person>
  58.   </People>‘,
  59.   ‘<?xml version="1.0" encoding="UTF-8"?>
  60.   <People xmlns="urn:ClientInfoNamespace">
  61.     <Person id="1234">
  62.       <FirstName>John</FirstName>
  63.       <LastName>Doe</LastName>
  64.     </Person>
  65.     <Person id="5678">
  66.       <FirstName>Jane</FirstName>
  67.       <LastName>Doe</LastName>
  68.     </Person>
  69.   </People>‘
  70. );

Listing 1: 创建测试环境和数据

The XML query() Method

query方法，通常被用来返回一个指定XML子集的无类型的XML实例，如下，用括号加单引号来实现表达式，语法：

db_object.query(‘xquery_exp‘)

当我们调用这个方法时，用真实数据库对象替换掉引号内的表达式。通过实例来比较一下结果有什么不一样。

  1. SELECT Info_untyped.query(‘/People‘)
  2.   AS People_untyped
  3. FROM ClientInfo;

Listing 2: 使用query() 来获得<People>元素中的值

在这种情况下，将返回标签下所有的元素，包括子元素属性以及它们的值。

  1. <People>
  2.   <Person id="1234">
  3.     <FirstName>John</FirstName>
  4.     <LastName>Doe</LastName>
  5.   </Person>
  6.   <Person id="5678">
  7.     <FirstName>Jane</FirstName>
  8.     <LastName>Doe</LastName>
  9.   </Person>
  10. </People>

Listing 3: 结果集返回了/People 的内容

假如打算检索类型化的列中的<People> 元素的内容，我需要修改XQuery的表达式。如Listing 4

  1. SELECT Info_typed.query(
  2.   ‘declare namespace ns="urn:ClientInfoNamespace";
  3.   /ns:People‘) AS People_typed
  4. FROM ClientInfo;

Listing 4: 使用query() 来检索类型化的XML列,然后你运行这个语句，就会得到结果如Listing5

  1. <People xmlns="urn:ClientInfoNamespace">
  2.   <Person id="1234">
  3.     <FirstName>John</FirstName>
  4.     <LastName>Doe</LastName>
  5.   </Person>
  6.   <Person id="5678">
  7.     <FirstName>Jane</FirstName>
  8.     <LastName>Doe</LastName>
  9.   </Person>
  10. </People>

Listing 5: 展示结果

如上，我们发现两种结果是很接近的，唯一的区别就是类型化的列里面包含了涉及的命名空间。

如果我们打算获得子下一级，子元素的内容，我们需要修改表达式，通过添加/Person 到路径名称中，如下：

  1. SELECT 
  2.   Info_untyped.query(
  3.     ‘/People/Person‘) AS People_untyped,
  4.   Info_typed.query(
  5.     ‘declare namespace ns="urn:ClientInfoNamespace";
  6.     /ns:People/ns:Person‘) AS People_typed
  7. FROM ClientInfo;

Listing 6: 检索 <Person> 元素

  1. <Person id="1234">
  2.   <FirstName>John</FirstName>
  3.   <LastName>Doe</LastName>
  4. </Person>
  5. <Person id="5678">
  6.   <FirstName>Jane</FirstName>
  7.   <LastName>Doe</LastName>
  8. </Person>

Listing 7: 这个结果集是非类型化数据的结果

  1. <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
  2.   <ns:FirstName>John</ns:FirstName>
  3.   <ns:LastName>Doe</ns:LastName>
  4. </ns:Person>
  5. <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
  6.   <ns:FirstName>Jane</ns:FirstName>
  7.   <ns:LastName>Doe</ns:LastName>
  8. </ns:Person>

Listing 8: 这个结果集是类型化数据的结果

如果我们打算去得到指定的<Person>下面的某一个元素，需要加入涉及的id属性。下面对比类型和非类型的两种情况下指定元素属性时如何获取。

  1. SELECT 
  2.   Info_untyped.query(
  3.     ‘/People/Person[@id=1234]‘) AS People_untyped,
  4.   Info_typed.query(
  5.     ‘declare namespace ns="urn:ClientInfoNamespace";
  6.     /ns:People/ns:Person[@id=5678]‘) AS People_typed
  7. FROM ClientInfo;

Listing 9: 检索数据，指定元素

前面的没有变化，按照元素来添加表达式，然后用中括号，在中括号内添加了@id的值，结果如下

  1. <Person id="1234">
  2.   <FirstName>John</FirstName>
  3.   <LastName>Doe</LastName>
  4. </Person>

Listing 10: id为1234非类型化数据结果返回值。

对于类型化的列，我使用的id为5678.注意，这次不再需要在属性名称前加上命名空间的前缀了，只需要在元素名字前引用就足够了。

  1. <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
  2.   <ns:FirstName>Jane</ns:FirstName>
  3.   <ns:LastName>Doe</ns:LastName>
  4. </ns:Person>

Listing 11: id为5678的数据结果

更进一步的展示结果，向下一级

  1. SELECT 
  2.   Info_untyped.query(
  3.     ‘/People/Person[@id=1234]/FirstName‘) AS People_untyped,
  4.   Info_typed.query(
  5.     ‘declare namespace ns="urn:ClientInfoNamespace";
  6.     /ns:People/ns:Person[@id=5678]/ns:FirstName‘) AS People_typed
  7. FROM ClientInfo;

结果

  1. <FirstName>John</FirstName>
  1. <ns:FirstName xmlns:ns="urn:ClientInfoNamespace">Jane</ns:FirstName>

Listing 14: 名字的结果的展示

当然还可以通过数字索引的方式展示：

  1. SELECT 
  2.   Info_untyped.query(
  3.     ‘/People/Person[1]/FirstName‘) AS People_untyped,
  4.   Info_typed.query(
  5.     ‘declare namespace ns="urn:ClientInfoNamespace";
  6.     /ns:People/ns:Person[2]/ns:FirstName‘) AS People_typed
  7. FROM ClientInfo;

Listing 15: 使用数字索引来引用元素下的结果

XML的value（）方法

就如同query()方法一样简便，很多时候当你想去检索一个特定的元素或属性的时候，而不是获取XML的元素，那就可以使用value()了。这种方法只会返回一个特定的值，不作为数据类型。因此一定要传递两个参数XQuery表达式和T-SQL数据类型。下面看语法：

db_object.value(‘xquery_exp‘, ‘sql_type‘)

  1. SELECT 
  2.   Info_untyped.value(
  3.     ‘(/People/Person[1]/FirstName)[1]‘, 
  4.     ‘varchar(20)‘) AS Name_untyped,
  5.   Info_typed.value(
  6.     ‘declare namespace ns="urn:ClientInfoNamespace";
  7.     (/ns:People/ns:Person[2]/ns:FirstName)[1]‘,
  8.     ‘varchar(20)‘) AS Name_typed
  9. FROM ClientInfo;

Listing 16: 检索<FirstName> 的值

在Listing16中，我指定了[1]在Xquery表达式的后面，所以结果集将只返回第一个人的名字。

  1. Name_untyped         Name_typed
  2. -------------------- --------------------
  3. John                 Jane

Listing 17: <FirstName>的两个结果

当然，我们也可以检索每个实例的id的属性值，并且指定Int类型返回。

  1. SELECT 
  2.   Info_untyped.value(
  3.     ‘(/People/Person/@id)[1]‘, 
  4.     ‘int‘) AS Name_untyped,
  5.   Info_typed.value(
  6.     ‘declare namespace ns="urn:ClientInfoNamespace";
  7.     (/ns:People/ns:Person/@id)[2]‘,
  8.     ‘int‘) AS Name_typed
  9. FROM ClientInfo;

Listing 19: 检索两个实例的id属性值

  1. Name_untyped         Name_typed
  2. -------------------- --------------------
  3. 1234                 5678

Listing 20: 返回两个id的属性

除了在表达式中定义你的XQuery表达式，你也能聚合的功能来进一步定义你的查询和操作数据。例如，count()功能，我们来获取每个列中<Person> 元素的个数。

  1. SELECT 
  2.   Info_untyped.value(
  3.     ‘count(/People/Person)‘, 
  4.     ‘int‘) AS Number_untyped,
  5.   Info_typed.value(
  6.     ‘declare namespace ns="urn:ClientInfoNamespace";
  7.     count(/ns:People/ns:Person)‘,
  8.     ‘int‘) AS Number_typed
  9. FROM ClientInfo;

Listing 21: 使用count功能来检索元素个数

结果如下：

  1. Number_untyped Number_typed
  2. -------------- ------------
  3. 2              2

Listing 22: 每列数据中<Person> 元素的数量

另外一个常用的功能是concat(), 它可以连接两个或多个XML元素下的数据。你可以指定你想连接的每一个部分。示例：

  1. SELECT 
  2.   Info_untyped.value(
  3.     ‘concat((/People/Person/FirstName)[2], " ", 
  4.       (/People/Person/LastName)[2])‘, 
  5.     ‘varchar(25)‘) AS FullName
  6. FROM ClientInfo;

Listing 23: 使用concat()来连接数值

  1. FullName
  2. -------------------------
  3. Jane Doe

Listing 24: 连接后的返回值

名和姓被连接起来，组成一个单一的值。都来自于同一个<Person> 下，当然也可以来自不同。

总结

 

我们基本上了解了XML在SQLServer 中的简单应用，从定义到使用方法。也看到了query（）检索子集，也能使用value（）检索独立的元素属性的值。当然除此之外还有向exist() andnodes() 这样方法，配合语法都以应用，这部分就不再展开讲了，大同小异。有不明白的可以私聊。更多使用方法还请访问MSDN来获取（搜索XQuery language reference）。

XML 在SQLServer中的使用

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！