您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 构建和转换XML,在SQLServer中的使用

构建和转换XML,在SQLServer中的使用

发布时间:2019-08-17 09:36编辑:1010cc安卓版浏览(178)

    XML查询技术

    XML文档以一个纯文本的形式存在,主要用于数据存储。不但方便用户读取和使用,而且使修改和维护变得更容易。

     

    在 Oracle 数据库 10g 第 2 版中,Oracle 引入了一个与该数据库集成的全功能自带 XQuery 引擎,该引擎可用于完成与开发支持 XML 的应用程序相关的各种任务。XQuery 是一种用于处理 XML 数据模型的查询语言,它实际上可操作任何类型的可用 XML 表达的数据。尽管 Oracle XQuery 实施使您可以使用数据库数据和外部数据源,但在处理数据库中存储的结构化数据方面,Oracle XML DB 通常可以显著提高性能。

    原文地址:

    简介

    Teradata数据库 (以下简称TD) 为存储和处理XML数据提供以下支持:

    • XML数据类型,允许用户以紧密二进制形式存储XML内容,用来保留XML文档的信息集
    • 支持常见XML操作,如解析,验证,转换(XSLT)和查询(XPath和XQuery)等和方法
    • 用于查询和转换XML内容的XQuery查询语言
    • 存储过程,允许用户以XML格式发布SQL查询的结果
    • 粉碎功能,允许用户从XML文档中提取值并使用它们来更新数据库表

    XML数据类型

    XML是SQL Server中内置的数据类型,可用于SQL语句或者作为存储过程的参数。用户可以直接在数据库中存储、查询和管理XML文件。XML数据类型还能保存整个XML文档。XML数据类型和其他数据类型不存在根本上的差别,可以把它用在任何普通SQL数据类型可以使用的地方。
    示例1:创建一个XML变量并用XML填充

    DECLARE @doc XML
    SELECT @doc='<Team name="Braves" />';
    

    示例2:创建XML数据类型列

    CREATE TABLE t1(
    column1 INT,
    column2 XML,
    CONSTRAINT pk_column1 PRIMARY KEY(column1));
    

    在上面的示例中,column2列是XML数据类型列。
    示例3:不能将XML数据类型列设置为主键或外键

    CREATE TABLE t1(
    column1 INT,
    column2 XML,
    CONSTRAINT pk_column1 PRIMARY KEY(column2));
    

    执行上面的代码,报错如下:
    消息1919,级别16,状态1,第1 行
    表't1' 中的列'column2' 的类型不能用作索引中的键列。
    消息1750,级别16,状态0,第1 行
    无法创建约束。请参阅前面的错误消息。
    XML数据类型的使用限制
    只有STRING数据类型才能转换成XML。
    XML列不能应用于GROUP BY语句中
    XML数据类型存储的数据不能超过2GB
    XML数据类型字段不能被设置成主键或者外键或称为其一部分。
    Sql_variant数据类型字段的使用不能把XML数据类型作为种子类型。
    XML列不能指定为唯一的。
    COLLATE子句不能被使用在XML列上。
    存储在数据库中的XML仅支持128级的层次。
    表中最对只能拥有32个XML列。
    XML列不能加入到规则中。
    唯一可应用于XML列的内置标量函数是ISNULL和COALESCE。
    具有XML数据类型列的表不能有一个超过15列的主键。

    SQL Server对于XML支持的核心在于XML数据的格式,这种数据类型可以将XML的数据存储于数据库的对象中,比如variables, columns, and parameters。当你用XML数据类型配置这些对象中的一个时,你指定类型的名字就像你在SQLServer 中指定一个类型一样。

    本文提供的示例不仅演示了在什么场合下以及如何使用 XQuery 查询、构建和转换 XML,而且还演示了如何监控和分析 XQuery 表达式的性能执行,从而找到更高效的方法来处理同一工作负载。

    XML 数据类型

    TD为XML数据提供了XML数据类型。你可以像使用其他TD支持的SQL数据类型一样使用它。它可以保留XML文档的信息集,包括从XML验证派生的层次结构信息和类型信息。XML类型可以存储最大2GB的XML数据,并支持XML处理功能。

    类型化的XML和非类型化的XML

    可以创建xml类型的变量,参数和列,或者将XML架构集合和xml类型的变量、参数或列关联,这种情况下,xml数据类型实例称之为类型化xml实例。否则XML实例称为非类型化的实例。

    XML的数据类型确保了你的XML数据被完好的构建保存,同时也符合ISO的标准。在定义一个XML数据类型之前,我们首先要知道它的几种限制,如下:

    基于关系数据构建 XML

     

    创建新的XML类型实例

    你可以通过以下方法创建XML类型实例:

    • New 操作符
    • CREATEXML 函数
    • XMLPARSE 函数

    例如以下例子,我们假设XML数据以文本形式被载入到customerText表的customerXMLText (VARCHAR或者CLOB类型)的列中。然后我们可以利用New 操作符来从以文本表达的XML数据中创建一个XML实例。

    SELECT customerID, (NEW XML(customerXMLText)).XMLEXTRACT('/Customer/Address', NULL)
    FROM customerText;
    

    查询的结果如下:

    customerID NEW XML(customerXMLText).XMLEXTRACT('/Customer/Address', Null)
    -------------------------------------------------------------------------
    1 <Address>100 1st Street, San Francisco, CA 94118</Address>
    

    同样地我们也用用CREATEXML来创建相应实例:

    SELECT customerID, (CREATEXML(customerXMLText)).XMLEXTRACT('/Customer/Address', NULL)
    FROM customerText;
    

    XML数据类型方法

    XML数据类型共有5种方法
    query():执行一个XML查询并返回查询结果(返回一个XML数据类型)。
    示例4

    DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
    SET @xmlDoc='<students>
        <class name="数学" NO="8501">
            <student>
                <name>李林</name>
                <sex>男</sex>
                <age>16</age>
                <address>江苏</address>
            </student>
        </class>
    </students>'--将XML实例分配给变量@xmlDoc
    SELECT @xmlDoc.query('/students/class/student') AS test
    --用query()查询@xmlDoc变量实例中标签<student>的子元素
    

    查询结果如图所示
    图片 1
    点击查询结果
    图片 2
    如想查询标签

    DECLARE @addr XML--声明一个XML类型变量@addr
    SET @addr='/students/class/student'
    SELECT @addr.exist('/students/class="江苏"') AS 返回值
    

    结果如图所示
    图片 3

    注:exsit()方法的参数不必做精确定位

    Value():计算一个查询并从XML中返回一个简单的值(只能返回单个值,且该值为非XML数据类型)。
    Value()方法有2个参数XQuery和SQLType,XQuery参数表示命令要从XML实例内部查询数据的具体位置,SQLType参数表示value()方法返回的值的首选数据类型。
    示例6

    DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
    DECLARE @classID INT--声明INT类型的变量@classID
    SET @xmlDoc='<students>
        <class name="数学" NO="8501">
            <student>
                <name>李林</name>
                <sex>男</sex>
                <age>16</age>
                <address>江苏</address>
            </student>
        </class>
    </students>'--将XML实例分配给变量@xmlDoc
    SET @classID=@xmlDoc.value('(/students/class/@NO)[1]','INT')
    --将value()方法返回值赋值给变量@classID
    SELECT @classID AS classID
    

    查询结果如图所示
    图片 4

    注:SQLType不能是XML数据类型,公共语言运行时(CLR)用户定义类型,image,text,ntext或sql_variant数据类型,但可以是用户自定义数据类型SQL。

    Modify():在XML文档的适当位置执行一个修改操作。它的参数XML_DML代表一串字符串,根据此字符串表达式来更新XML文档的内容。
    示例7:在@xmlDoc的实例中,元素

    DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
    SET @xmlDoc='<students>
        <class name="数学" NO="8501">
            <student>
                <name>李林</name>
                <sex>男</sex>
                <age>16</age>
                <address>江苏</address>
            </student>
        </class>
    </students>'
    SELECT @xmlDoc AS '插入节点前信息'
    SET @xmlDoc.modify('insert <学历>本科</学历> after (students/class/student/age)[1]')
    SELECT @xmlDoc AS '插入节点后信息'
    

    查询结果插入节点后信息如图所示
    图片 5

    注:modify()方法的参数中insert和其他关键字必须小写,否则会报错

    Nodes():允许把XML分解到一个表结构中。此方法将XML数据类型实例拆分为关系数据,并返回包含原始XML数据的行集。
    示例8:依然用@locat参数的实例来示范

    DECLARE @locat XML--声明XML变量@locat
    SET @locat=
    '<root>
        <location locationID="8">
            <step>8的步骤</step>
            <step>8的步骤</step>
            <step>8的步骤</step>
        </location>
        <location locationID="9">
            <step>9的步骤</step>
            <step>9的步骤</step>
            <step>9的步骤</step>
        </location>
        <location locationID="10">
            <step>10的步骤</step>
            <step>10的步骤</step>
            <step>10的步骤</step>
        </location>
        <location locationID="11">
            <step>11的步骤</step>
            <step>11的步骤</step>
            <step>11的步骤</step>
        </location>
    </root>'--@locat变量的实例
    
    SELECT T.Loc.query('.') AS result
    FROM @locat.nodes('/root/location') T(Loc)
    GO
    

    查询结果如下图所示
    图片 6

    • 一个实例的XML列不能包含超过2GB的数据。
    • 一个XML的列不能是索引。
    • XML对象不能使用Group By的子句中。
    • XML的数据类型不支持比较和排序。

    在需要的情况下(例如,向 Web 服务发送结果),您可能要基于关系数据构建 XML。要在 Oracle 数据库 10g 第 2 版之前的版本中完成此任务,通常需要使用 SQL/XML 生成函数,如 XMLElement、XMLForest 和 XMLAgg()。在 Oracle 数据库 10 g 第 2 版中,XQuery 将比这些函数更为高效。具体而言,在 XQuery 表达式内部使用 ora:view XQuery 函数,您可以查询现有的关系表或视图以及即时构建 XML,从而不必通过关系数据显式创建 XML 视图。列表 1 中的 PL/SQL 代码演示了如何使用 ora:view 基于示例数据库模式 HR 的默认员工关系表中存储的数据构建 XML 文档。

    引用:

    用法

    你可以指定XML类型:

    • 在表定义中
      注意:XML类型是LOB类型,最多一行占用4096个字节。考虑到一个64K的行大小,你可以在单个表上定义不超过15个XML类型的列。
    • 作为存储过程中的IN,OUT或INOUT参数
    • 作为结构化UDT的一部分

    INSERT and UPDATE statements can also assign XML type values to columns.

    XQuery简介

    XQuery是一种查询语言,可以查询结构化或者半结构化的数据。SQL Server 2008中对XML数据类型提供了支持,可以存储XML文档,然后使用XQuery语言进行查询。

    定义一个XML变量

    列表 1:使用 ora:view 基于关系数据创建 XML

    摘要

    限制

    你不能将XML类型指定为:

    • UDF或UDM中的输入参数或结果类型
    • 外部存储过程中的IN,OUT或INOUT参数

    UDF,UDM和外部存储过程只能将XML数据处理为VARCHAR,CLOB,VARBYTE或BLOB值; 因此,在将XML值传递给外部例程之前,必须使用XMLSERIALIZE函数将XML值序列化为VARCHAR,CLOB,VARBYTE或BLOB值。 您可以使用CREATEXML函数将外部例程的结果转化为XML类型值。

    • XML类型可以容纳最大2GB的值。However, operations like XSLT and XQuery are only supported on documents that are smaller in size where the processing operation does not require more memory than specified by the XML_MemoryLimit DBS Control field.
    • XML类型的列不能:
      • 存在于队列表中
      • 成为索引的一部分
      • 参与JOIN操作
    • 你不能在依赖于排序或比较的子句中使用XML类型的列,例如ORDER BY,GROUP BY或HAVING。
    • 你不能在算术表达式中使用XML值。 XML值可以是一个类型,如xs:int,可用于算术计算。 在这种情况下,您可以将XML值转换为适当的SQL类型来执行计算。
    • XML类型值不具有可比性,不应在关系比较操作中使用(例如>,<,=)。 XML值可以显式转换为其他标量SQL类型,这些类型的值可能是可比较的。 例如,如果已知该值是XML类型xs:int,则可以将其转换为SQL整数数据类型。

    注意:因为XML值不具有可比性,所以它们不包含在对行重复的检查中(例如,在插入到一个集表中)。 这种行为类似于也不参与行重复检查的CLOB / BLOB类型。

    • 尽管XML值的外部表示是字符串类型,但是字符串操作不能直接在XML值上使用。 在应用字符串操作之前,可以对XML进行序列化或强制转换以生成字符串表示。

    FOR XML子句

    通过在SELECT语句中使用FOR XML子句可以把数据库表中的数据检索出来并生成XML格式。SQL Server 2008支持FOR XML的四种模式,分别是RAW模式,AUTO模式,EXPLICIT模式和PATH模式。

    DECLARE @ClientList XML
    SET @ClientList =
    '<?xml version="1.0" encoding="UTF-8"?>
    <!-- A list of current clients -->
    <People>
    <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
    </Person>
    </People>'
    SELECT @ClientList
    GO
    
    BEGIN
    IF(DBMS_XDB.CREATEFOLDER('/public/employees')) THEN
    DBMS_OUTPUT.PUT_LINE('Folder is created');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Cannot create folder');
    END IF;
    COMMIT;
    END;
    /
    DECLARE
    XMLdoc XMLType;
    BEGIN
    SELECT XMLQuery(
    'for $j in 1
    return (
    {
    for $i in ora:view("HR", "employees")/ROW
    where $i/EMPLOYEE_ID <= 102
    return (
    {xs:string($i/EMPLOYEE_ID)}
    {xs:string($i/LAST_NAME)}
    {xs:integer($i/SALARY)}
    )} )'
    RETURNING CONTENT) INTO XMLdoc FROM DUAL;
    IF(DBMS_XDB.CREATERESOURCE('/public/employees/employees.xml', XMLdoc)) THEN
    DBMS_OUTPUT.PUT_LINE('Resource is created');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Cannot create resource');
    END IF;
    COMMIT;
    END;
    /

    本文介绍了SQL Server 2005能够支持的XQuery的各方面特性如FLWOR语句,XQuery的操作,if-then-else结构,XML的构造函数,XQuery的内置函数,类型的转换操作符,并举例说明了上述XQuery功能的应用和操作。本文同时也对SQL Server 2005所不能支持的XQuery的特性进行了分析讨论,并提出了实际工作中的解决方案。在本文的最后章节中,详细举例介绍了三种特定的XQuery应用场景。

    排序

    XML值不具有可比性。 在任何依赖比较值(如ORDER BY,GROUP BY或DISTINCT)的子句中引用XML类型列都会导致错误。

    FOR XML RAW

    将表转换成元素名称是row,属性名称为列名或者列的别名。
    示例9:将Student表转换为XML格式(FOR XML RAW)
    Student表的数据如图所示
    图片 7
    执行语句:

    SELECT * FROM Student FOR XML RAW;
    

    查询结果如图所示
    图片 8
    图片 9

    这个例子通过使用DECLARE  声明去定义名为@ClientList 的变量,当我声明变量的时候,只需要包含XML的数据类型的名字在变量名后。

    在列表 1 中的第一个 PL/SQL 过程中,您只是在 XML 信息库中创建了一个新文件夹。在该信息库文件夹中,您随后将存储此处显示的第二个 PL/SQL 过程中创建的 XML 文档。第二个 PL/SQL 过程首先发出 SELECT 语句,该语句使用 XMLQuery SQL 函数基于关系数据构建 XML。对于 XQuery 表达式(XMLQuery 在此处将其用作参数)而言,请注意嵌套的 FLWOR 表达式中使用的 ora:view XQuery 函数。在该示例中,ora:view 获取两个输入参数,即“HR”和“employees”,它们指示该函数查询属于 HR 数据库模式的员工表。因此,ora:view 将返回一个表示 HR.employees 表行的员工 XML 文档序列。但为了节省结果文档中的空间,只将前三个员工记录传递给结果序列。这是通过在 FLWOR 表达式的 where 子句中指定 $i/EMPLOYEE_ID <= 102 而实现的。请注意 FLWOR 表达式的 return 子句中使用的 xs:string()xs:integer() XQuery 类型表达式。实际上,此处使用的这两个 XQuery 表达式不仅将 XML 节点值转换为相应的类型,而且还将提取这些节点值。随后,生成的员工 XML 文档作为 employees.xml 保存到之前在列表 1 中另一个 PL/SQL 过程中创建的 /public/employees XML 信息库文件夹。要确保此操作已完成,可执行以下查询:

    引用:

    转换

    从其他SQL类型转换为XML类型时,an XML value of the nearest xml schema primitive type is created. 。 例如,VARCHAR / CLOB的xs:string和DATE的xs:date。在向另一个方向转换时,XML实例的字符串值应该与目标数据类型兼容。 例如,将xs:date值转换为SQL DATE类型。
    以下数据类型支持向和从XML类型转换。
    VARCHAR | CLOB | VARBYTE | BLOB | BYTEINT | SMALLINT
    INTEGER | DATE | DECIMAL | FLOAT | NUMBER |BIGINT
    TIME | TIME WITH TIMEZONE
    TIMESTAMP | TIMESTAMP WITH TIMEZONE

    注意:从大对象类型(CLOB和BLOB)转换为XML类型受限于其大小,只有小于64K才能成功转换。

    FOR XML AUTO

    使用表名称作为元素名称,使用列名称作为属性名称,SELECT关键字后面列的顺序用于XML文档的层次。
    示例10:将Student表转换为XML格式(FOR XML AUTO)
    执行语句:

    SELECT * FROM Student FOR XML AUTO;
    

    查询结果如图所示
    图片 10
    图片 11

    我设定了变量的值,然后使用select 来检索这个值。和我们想的一样,它返回了XML的文档。如下:

    SELECT XMLQuery('for $i in fn:doc("/public/employees/employees.xml")
    return;
    $i'
    RETURNING CONTENT) AS RESULT FROM DUAL;

    内容目录

    数据迁移

    要将存储在VARCHAR或CLOB列中的XML数据迁移到具有XML类型列的等效模式,请执行以下操作:

    1. 验证XML数据是否格式正确,并符合XML格式的规则。
    2. 使用XML类型为保存XML数据的列创建新版本的表。
    3. 使用NEW XML运算符或CREATEXML函数将XML文本插入到XML列中。
    FOR XML EXPLICIT

    允许用户显式地定义XML树的形状,不受AUTO模式中的种种限制。不能将FOR XML EXPLICIT直接用在SELECT子句中。
    示例11:将xmlTest表转换为XML格式(FOR XML EXPLICIT)
    XmlTest表的数据如图所示
    图片 12

    SELECT DISTINCT 1 AS TAG,--指定顶级层级序号1
    NULL AS PARENT,--该层级没有父级
    NULL AS '班级信息!1!',
    NULL AS '班级信息!2!班级',
    NULL AS '班级信息!2!班级类型',
    NULL AS '班级信息!2!班主任',
    NULL AS '学生信息!3!学号!Element',
    NULL AS '学生信息!3!学生姓名!Element',
    NULL AS '学生信息!3!性别!Element',
    NULL AS '学生信息!3!总分!Element'--设置所有层级元素和属性命名,暂时不对这些元素赋值
    --例如在“学生信息!3!总分!Element”格式中,学生信息是元素名,3表示该元素所处层级,总分表示属性名
    --Element指出生成以属性单独为一行的XML格式
    UNION ALL--层级之间用UNION ALL相连
    SELECT DISTINCT 2 AS TAG,--指定二级层级序号2
    1 AS PARENT,--父级序号是序号为1的层级
    NULL,--在层级的代码中已列出了所有层级元素和属性命名,因此这里给元素和属性做赋值。这句语句对应层级代码中“NULL AS '班级信息!1!'”,说明我希望该元素作为独立成行的标签,没有赋值。
    班级,--对层级中的“NULL AS '班级信息!2!班级'”赋值,将xmlTest表中的班级赋值给属性班级
    班级类型,--对层级中的“NULL AS '班级信息!2!班级类型'”赋值,将xmlTest表中的班级赋值给属性班级类型
    班主任,--同上
    NULL,--这句语句开始对应的是层级的属性,因此在层级的代码中不做赋值,在下面层级的代码中做赋值
    NULL,
    NULL,
    NULL
    FROM xmlTest--指出上面赋值的数据源来自于xmlTest表
    UNION ALL--各个层级之间用UNION ALL连接
    SELECT 3 AS TAG,--指定3级层级序号3
    2 AS PARENT,--父级是序号为2的层级
    NULL,--对应层级的”NULL AS '班级信息!1!'“语句,不希望它有值,所以不做赋值
    NULL,--这三个NULL对应层级的各个属性,在层级的代码中已经做过赋值,因此在这里不做赋值
    NULL,
    NULL,
    学号,--对应层级1代码中的层级3属性,在层级代码3中进行赋值
    学生姓名,
    性别,
    年级总分
    FROM xmlTest
    FOR XML EXPLICIT;--将上述查询转换为XML,不能漏掉,否则结果会以表格形式显示
    

    查询结果如图所示
    图片 13
    图片 14
    在结果图中我们发现,红框中3个班级信息列在一起,而所有学生都列在高一3班下,这不是我们想要的结果,我们希望每个班级对应自己的学生。那么如何解决此类问题呢,这涉及到排序。

    注:如果层级中有多个数据完全重复,可以在该层级对应的代码前加DISTINCT关键字去除重复元素。

    首先删除代码行末的FOR XML EXPLICIT语句,仅仅执行剩下的部分,使结果以表格形式呈现,那么结果如下
    图片 15
    这个表格每行的顺序也代表了该表格转化为XML文档后内容显示顺序。图中层级2(TAG=2)的几行,位置都在一起,这也就是为什么层级3的所有数据都在高一3班下面了。我们需要对表格每行的顺序进行调整,使学生所在行按照xmlTest表中的数据逻辑分散在班级行之下。但是根据上面的表格发现,不管按照什么字段排序,都不可能达到效果。
    正确代码如下

    SELECT DISTINCT 1 AS TAG,
    NULL AS PARENT,
    NULL AS '班级信息!1!',
    NULL AS '班级信息!2!班级',
    NULL AS '班级信息!2!班级类型',
    NULL AS '班级信息!2!班主任',
    NULL AS '学生信息!3!学号!Element',
    NULL AS '学生信息!3!学生姓名!Element',
    NULL AS '学生信息!3!性别!Element',
    NULL AS '学生信息!3!总分!Element'
    UNION ALL
    SELECT DISTINCT 2 AS TAG,
    1 AS PARENT,
    NULL,
    班级,
    班级类型,
    班主任,
    NULL,
    NULL,
    NULL,
    NULL
    FROM xmlTest
    UNION ALL
    SELECT 3 AS TAG,
    2 AS PARENT,
    NULL,
    班级,
    班级类型,
    班主任,
    学号,
    学生姓名,
    性别,
    年级总分
    FROM xmlTest
    ORDER BY [班级信息!2!班级],[学生信息!3!学号!Element]
    FOR XML EXPLICIT;
    

    对比第一次代码,我们发现上面的代码不止在行末对数据按元素属性进行了排序,还在赋值的代码中有所改动。在层级1代码中完全没有改动,因为层级1的代码作用是设置XML格式的,对数据排序没有影响。在下面几个层级的赋值部分,每个层级的代码中都对上面几个层级的元素重复赋值,这样做使结果的表格中不再有那么多属性值是NULL,可以方便排序。最后再按照元素[班级信息!2!班级]和[学生信息!3!学号!Element]排序。让我们看看结果如何。
    运行上面的代码,但不运行FOR XML EXPLICIT语句,看看表格中数据内容和行顺序是否改变
    图片 16
    如图所示,发现行数据和学生数据的顺序显示正确。运行所有代码得到XML文档,结果如图所示
    图片 17
    由于XML文档内容过长,不贴图了,直接复制所有XML内容展示一下。

    <班级信息>
      <班级信息 班级="高一1班" 班级类型="创新班" 班主任="李玉虎">
        <学生信息>
          <学号>20180101</学号>
          <学生姓名>李华</学生姓名>
          <性别>男</性别>
          <总分>5.680000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180103</学号>
          <学生姓名>孙丽</学生姓名>
          <性别>女</性别>
          <总分>3.390000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180108</学号>
          <学生姓名>吴伟</学生姓名>
          <性别>男</性别>
          <总分>5.280000000000000e 002</总分>
        </学生信息>
      </班级信息>
      <班级信息 班级="高一2班" 班级类型="重点班" 班主任="姜杰">
        <学生信息>
          <学号>20180102</学号>
          <学生姓名>张三</学生姓名>
          <性别>男</性别>
          <总分>6.270000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180104</学号>
          <学生姓名>袁康</学生姓名>
          <性别>男</性别>
          <总分>4.820000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180106</学号>
          <学生姓名>赵四</学生姓名>
          <性别>男</性别>
          <总分>5.680000000000000e 002</总分>
        </学生信息>
      </班级信息>
      <班级信息 班级="高一3班" 班级类型="提高班" 班主任="师从光">
        <学生信息>
          <学号>20180105</学号>
          <学生姓名>王婷</学生姓名>
          <性别>女</性别>
          <总分>7.610000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180107</学号>
          <学生姓名>周其</学生姓名>
          <性别>女</性别>
          <总分>3.480000000000000e 002</总分>
        </学生信息>
        <学生信息>
          <学号>20180109</学号>
          <学生姓名>甄诚</学生姓名>
          <性别>女</性别>
          <总分>7.020000000000000e 002</总分>
        </学生信息>
      </班级信息>
    </班级信息>
    

    将上面的结果对比一下原始xmlTest表,看看每个班级和它下属学生的层级关系是否有误。

    注:写FOR XML EXPLICIT代码要注意,层级1的代码中先设置层级结构,不要先急着赋值。在下属层级的代码中对层级1中的代码进行赋值,最好重复赋值,不然就会出现文中的排序问题。如果某个层级出现重复数据,在该层级的代码前加DISTINCT关键字。解决排序问题最好的办法是对各个层级的属性重复赋值并在末尾用ORDER BY按层级属性排序。

    仔细观察上面的XML文档,发现总分属性的值是个float类型,要把它转换成int,只需要把层级3中对总分的赋值代码改成CAST(年级总分 AS int)
    图片 18

    <!-- A list of current clients -->
    <People>
    <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
    </Person>
    </People>
    

    该查询应生成以下输出:

    导言

    范例

    该语句创建一个包含XML类型列customerXML的表:

    CREATE TABLE customer (
        customerID INTEGER,
        customerName VARCHAR(256),
        customerXML XML 
    )  PRIMARY INDEX (customerID);
    
    FOR XML PATH

    PATH模式提供了一种较简单的方法来混合元素及属性。在PATH模式中,列名或列别名被作为XPATH表达式来处理,这些表达式指定了如何将值映射到XML中。默认情况下,PATH模式为每一样自动生成

     

    
    100
    King
    24000
    
    
    101
    Kochhar
    17000
    
    
    102
    De Haan
    17000
    

    SQL Server 2005中的XML数据类型

    没有名称的列

    下面介绍一种简单的FOR XML PATH应用方式

    SELECT 2 3 FOR XML PATH;--将2 3的值转换成xml格式
    

    查询结果如图所示
    图片 19

    注:如果提供了空字符串FOR XML PATH(‘’)则不会生成任何元素。

    SELECT 2 3 FOR XML PATH('');--将2 3的值转换成xml格式并去掉<row>
    

    查询结果如图所示
    图片 20
    示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中成绩>=700分的学生的班主任信息和学生信息,并转化成XML格式
    XmlTest表数据如下图所示
    图片 21
    MainTeacher表数据如下图所示
    图片 22
    执行下面的语句

    SELECT xmlTest.学号 AS '学生信息/@学号',--@符号表示该名称为属性名,斜杠表示子层级
    xmlTest.学生姓名 AS '学生信息/@姓名',
    xmlTest.班级 AS '学生信息/@班级',
    mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
    mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
    mainTeacher.性别 AS '学生信息/班主任信息/性别',
    mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
    mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
    FROM xmlTest,mainTeacher
    WHERE xmlTest.年级总分>=700
    AND xmlTest.班主任=mainTeacher.姓名
    FOR XML PATH('result');--将根目录名改为result
    

    查询结果如下所示

    <result>
      <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
        <班主任信息>
          <姓名>师从光</姓名>
          <教师编号>83928182</教师编号>
          <性别>男</性别>
          <年龄>28</年龄>
          <联系电话>15963002120</联系电话>
        </班主任信息>
      </学生信息>
    </result>
    <result>
      <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
        <班主任信息>
          <姓名>师从光</姓名>
          <教师编号>83928182</教师编号>
          <性别>男</性别>
          <年龄>28</年龄>
          <联系电话>15963002120</联系电话>
        </班主任信息>
      </学生信息>
    </result>
    

    接下来我们看看如何定义一个XML的列

    在下面的例子中,我将创建一个商店客户的表,表中存储了ID和每个商店的客户信息。

    USE AdventureWorks2008R2
    GO
    IF OBJECT_ID('dbo.StoreClients') IS NOT NULL
    DROP TABLE dbo.StoreClients
    GO
    CREATE TABLE dbo.StoreClients
    (
    StoreID INT IDENTITY PRIMARY KEY,
    ClientInfo XML NOT NULL
    )
    GO
    

    接下来插入数据到这个表中,包括XML的文档和片段。我将声明一个XML的变量,然后用这个变量插入这个文档到表的数据行里面。

    DECLARE @ClientList XML
    SET @ClientList =
    '<?xml version="1.0" encoding="UTF-8"?>
    <!-- A list of current clients -->
    <People>
    <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
    </Person>
    </People>'
    INSERT INTO dbo.StoreClients (ClientInfo)
    VALUES(@ClientList)
    GO
    

    尽管变量将整个XML文档插入了进来,但是它是被当做一个单一的值插入到表列里面来。

    正如以上所述,创建和插入都是很直接简单的,接下来我们看一下如何创建一个XML的参数

    定义一个XML参数

    例如,我定义@StoreClients 作为一个输入参数,并且配置它为XML的类型

    USE AdventureWorks2008R2
    GO
    IF OBJECT_ID('dbo.AddClientInfo', 'P') IS NOT NULL
    DROP PROCEDURE dbo.AddClientInfo
    GO
    CREATE PROCEDURE dbo.AddClientInfo
    @StoreClients XML
    AS
    INSERT INTO dbo.StoreClients (ClientInfo)
    VALUES(@StoreClients)
    GO
    

    然后我们再看看在存储过程中如何使用XML作为参数:

    DECLARE @ClientList XML
    SET @ClientList =
    '<?xml version="1.0" encoding="UTF-8"?>
    <!-- A list of current clients -->
    <People>
    <Person id="1234">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
    </Person>
    </People>'
    EXEC dbo.AddClientInfo @ClientList
    

    过程也是很直接,先将XML数据赋值给变量,然后将变量作为参数执行SP,这是查询你会发现数据已经在表中了。

    现在我们要学习一下XML类型支持的方法:query(``), value().

    在这之前我们要知道一种表达式,就是XQuery,它是一种强大的脚本语言,用来获取XML的数据。SQLServer 支持这种语言的子集,所以我们能使用这种语言的表达式来检索和修改XML的数据。

    在以上 XQuery 中,fn:doc XQuery 函数用于访问 Oracle XML DB 信息库中存储的单个 XML 文档。但如果要处理一些具有相同或相似结构的 XML 文档(存储在同一 XML 信息库文件夹中),应该怎么做?这种情况下,另一个用于处理 XML 信息库资源的 XQuery 函数(即 fn:collection)可能会派上用场。本文稍后将介绍几个有关如何使用 fn:collection XQuery 函数的示例。

    类型化vs.非类型化的XML数据类型

    TYPE命令

    SQL Server支持TYPE命令将FOR XML的查询结果作为XML数据类型返回。
    示例13:依然是上面的例子,将查询结果作为XML数据类型返回。

    CREATE TABLE xmlType(xml_col XML);
    --首先创建一个表xmlType,只有一列xml数据类型的xml_col
    INSERT INTO xmlType
    SELECT(--将上面的查询语句全部复制到括号中,末尾加上TYPE,表示将XML文档作为xml数据类型,并插入到表xmlType中
    SELECT xmlTest.学号 AS '学生信息/@学号',
    xmlTest.学生姓名 AS '学生信息/@姓名',
    xmlTest.班级 AS '学生信息/@班级',
    mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
    mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
    mainTeacher.性别 AS '学生信息/班主任信息/性别',
    mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
    mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
    FROM xmlTest,mainTeacher
    WHERE xmlTest.年级总分>=700
    AND xmlTest.班主任=mainTeacher.姓名
    FOR XML PATH('result'),TYPE
    );
    SELECT * FROM xmlType;--查询xmlType表
    

    查询结果如图所示
    图片 23
    双击打开查看XML

    <result>
      <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
        <班主任信息>
          <姓名>师从光</姓名>
          <教师编号>83928182</教师编号>
          <性别>男</性别>
          <年龄>28</年龄>
          <联系电话>15963002120</联系电话>
        </班主任信息>
      </学生信息>
    </result>
    <result>
      <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
        <班主任信息>
          <姓名>师从光</姓名>
          <教师编号>83928182</教师编号>
          <性别>男</性别>
          <年龄>28</年龄>
          <联系电话>15963002120</联系电话>
        </班主任信息>
      </学生信息>
    </result>
    
    注意:

    因为XQuery是一种非常复杂的语言,我们只是涉及了一部分他的组件,如果想要更进一步的理解它如何应用,请查看MSDN XQuery language reference.

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

    USE master;
    GO
    
    IF DB_ID('ClientDB') IS NOT NULL
    DROP DATABASE ClientDB;
    GO
    
    CREATE DATABASE ClientDB;
    GO
    
    USE ClientDB;
    GO
    
    IF OBJECT_ID('ClientInfoCollection') IS NOT NULL
    DROP XML SCHEMA COLLECTION ClientInfoCollection;
    GO
    
    CREATE XML SCHEMA COLLECTION ClientInfoCollection AS 
    '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns="urn:ClientInfoNamespace" 
    targetNamespace="urn:ClientInfoNamespace" 
    elementFormDefault="qualified">
      <xsd:element name="People">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
                  <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
                  <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" />
                </xsd:sequence>
                <xsd:attribute name="id" type="xsd:integer" use="required"/>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>';
    GO
    
    IF OBJECT_ID('ClientInfo') IS NOT NULL
    DROP TABLE ClientInfo;
    GO
    
    CREATE TABLE ClientInfo
    (
      ClientID INT PRIMARY KEY IDENTITY,
      Info_untyped XML,
      Info_typed XML(ClientInfoCollection)
    );
    
    INSERT INTO ClientInfo (Info_untyped, Info_typed)
    VALUES
    (
      '<?xml version="1.0" encoding="UTF-8"?>
      <People>
        <Person id="1234">
          <FirstName>John</FirstName>
          <LastName>Doe</LastName>
        </Person>
        <Person id="5678">
          <FirstName>Jane</FirstName>
          <LastName>Doe</LastName>
        </Person>
      </People>',
      '<?xml version="1.0" encoding="UTF-8"?>
      <People xmlns="urn:ClientInfoNamespace">
        <Person id="1234">
          <FirstName>John</FirstName>
          <LastName>Doe</LastName>
        </Person>
        <Person id="5678">
          <FirstName>Jane</FirstName>
          <LastName>Doe</LastName>
        </Person>
      </People>'
    );
    

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

    查询 XMLType 数据

    XML数据类型的方法

    FOR XML的嵌套查询

    示例14:在示例12的查询结果中查询班主任联系电话

    SELECT (
    SELECT xmlTest.学号 AS '学生信息/@学号',
    xmlTest.学生姓名 AS '学生信息/@姓名',
    xmlTest.班级 AS '学生信息/@班级',
    mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
    mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
    mainTeacher.性别 AS '学生信息/班主任信息/性别',
    mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
    mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
    FROM xmlTest,mainTeacher
    WHERE xmlTest.年级总分>=700
    AND xmlTest.班主任=mainTeacher.姓名
    FOR XML PATH('result'),TYPE).query('result/学生信息/班主任信息/联系电话') AS '优秀教师联系方式';
    

    SELECT里面依然套用了示例13中被套用的代码,外面用了query方法,查询结果如下图所示
    图片 24

    <联系电话>15963002120</联系电话>
    <联系电话>15963002120</联系电话>
    

    The XML query() Method

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

    db``_object``.query('``xquery_exp``')

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

    SELECT Info_untyped.query('/People')
      AS People_untyped
    FROM ClientInfo;
    

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

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

    <People>
      <Person id="1234">
        <FirstName>John</FirstName>
        <LastName>Doe</LastName>
      </Person>
      <Person id="5678">
        <FirstName>Jane</FirstName>
        <LastName>Doe</LastName>
      </Person>
    </People>
    

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

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

    SELECT Info_typed.query(
      'declare namespace ns="urn:ClientInfoNamespace";
      /ns:People') AS People_typed
    FROM ClientInfo;
    

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

    <People xmlns="urn:ClientInfoNamespace">
      <Person id="1234">
        <FirstName>John</FirstName>
        <LastName>Doe</LastName>
      </Person>
      <Person id="5678">
        <FirstName>Jane</FirstName>
        <LastName>Doe</LastName>
      </Person>
    </People>
    

    Listing 5: 展示结果

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

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

    SELECT 
      Info_untyped.query(
        '/People/Person') AS People_untyped,
      Info_typed.query(
        'declare namespace ns="urn:ClientInfoNamespace";
        /ns:People/ns:Person') AS People_typed
    FROM ClientInfo;
    

    Listing 6: 检索 <Person> 元素

    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    <Person id="5678">
      <FirstName>Jane</FirstName>
      <LastName>Doe</LastName>
    </Person>
    

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

    <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="1234">
      <ns:FirstName>John</ns:FirstName>
      <ns:LastName>Doe</ns:LastName>
    </ns:Person>
    <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
      <ns:FirstName>Jane</ns:FirstName>
      <ns:LastName>Doe</ns:LastName>
    </ns:Person>
    

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

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

    SELECT 
      Info_untyped.query(
        '/People/Person[@id=1234]') AS People_untyped,
      Info_typed.query(
        'declare namespace ns="urn:ClientInfoNamespace";
        /ns:People/ns:Person[@id=5678]') AS People_typed
    FROM ClientInfo;
    

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

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

    <Person id="1234">
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
    

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

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

    <ns:Person xmlns:ns="urn:ClientInfoNamespace" id="5678">
      <ns:FirstName>Jane</ns:FirstName>
      <ns:LastName>Doe</ns:LastName>
    </ns:Person>
    

    Listing 11: id为5678的数据结果

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

    SELECT 
      Info_untyped.query(
        '/People/Person[@id=1234]/FirstName') AS People_untyped,
      Info_typed.query(
        'declare namespace ns="urn:ClientInfoNamespace";
        /ns:People/ns:Person[@id=5678]/ns:FirstName') AS People_typed
    FROM ClientInfo;
    

    结果

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

    Listing 14: 名字的结果的展示

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

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

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

    XQuery 使您可以操作基于 XML 模式以及非基于模式的数据。以下示例演示了如何使用 XMLTable 函数从 OE 演示数据库模式中查询基于 PurchaseOrder XML 模式的 XMLType 表。

    XQuery入门

    XML索引

    由于XML数据类型最大可存储2GB的数据,因此需要创建XML索引来优化查询性能。

    XML的value()方法

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

    db``_object``.value('``xquery_exp``', '``sql_type``')

    SELECT 
      Info_untyped.value(
        '(/People/Person[1]/FirstName)[1]', 
        'varchar(20)') AS Name_untyped,
      Info_typed.value(
        'declare namespace ns="urn:ClientInfoNamespace";
        (/ns:People/ns:Person[2]/ns:FirstName)[1]',
        'varchar(20)') AS Name_typed
    FROM ClientInfo;
    

    Listing 16: 检索<FirstName> 的值

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

    Name_untyped         Name_typed
    -------------------- --------------------
    John                 Jane
    

    Listing 17: <FirstName>的两个结果

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

    SELECT 
      Info_untyped.value(
        '(/People/Person/@id)[1]', 
        'int') AS Name_untyped,
      Info_typed.value(
        'declare namespace ns="urn:ClientInfoNamespace";
        (/ns:People/ns:Person/@id)[2]',
        'int') AS Name_typed
    FROM ClientInfo;
    

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

    Name_untyped         Name_typed
    -------------------- --------------------
    1234                 5678
    

    Listing 20: 返回两个id的属性

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

    SELECT 
      Info_untyped.value(
        'count(/People/Person)', 
        'int') AS Number_untyped,
      Info_typed.value(
        'declare namespace ns="urn:ClientInfoNamespace";
        count(/ns:People/ns:Person)',
        'int') AS Number_typed
    FROM ClientInfo;
    

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

    结果如下:

    Number_untyped Number_typed
    -------------- ------------
    2              2
    

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

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

    SELECT 
      Info_untyped.value(
        'concat((/People/Person/FirstName)[2], " ", 
          (/People/Person/LastName)[2])', 
        'varchar(25)') AS FullName
    FROM ClientInfo;
    

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

    FullName
    -------------------------
    Jane Doe
    

    Listing 24: 连接后的返回值

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

    SELECT ttab.COLUMN_VALUE AS OrderTotal FROM purchaseorder,
    XMLTable(
    'for $i in /PurchaseOrder
    where $i/User = "EABEL"
    return;
    
    {$i/Reference}
    
    {fn:sum(for $j in $i/LineItems/LineItem/Part
    return ($j/@Quantity*$j/@UnitPrice))}
    
    '
    PASSING OBJECT_VALUE
    ) ttab;

    XPath2.0简介

    主XML索引

    主XML索引对XML列中XML实例内的所有标记,值和路径进行索引。创建主XML索引时,相应XML列所在的表必须对该表的主键创建了聚集索引。

    总结

     

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

    在以上示例中,您在 XMLTable 函数的 PASSING 子句中使用 OBJECT_VALUE 虚拟列将 purchaseorder 表作为上下文项传递给此处使用的 XQuery 表达式。XQuery 表达式计算用户 EABEL 请求的每个购买订单的总计,并为处理的每个订单生成一个 OrderTotal XML 元素。要访问生成的 XML,请使用 SELECT 列表中的 COLUMN_VALUE 虚拟列。最终的输出应如下所示:

    XQuery简介

    辅助XML索引

    为了增强主XML索引的性能,可以创建辅助XML索引。只有创建了主XML索引后才能创建辅助XML索引。辅助XML索引分3种:PATH,VALUES和PROPERTY辅助XML索引。

    ORDERTOTAL
    -------------------------------------------------------------
    
    EABEL-20021009123338324PDT
    1328.05
    
    
    EABEL-20021009123335791PDT
    2067.15
    
    
    EABEL-20021009123336251PDT
    289.6
    
    
    EABEL-20021009123336382PDT
    928.92

    XQuery的优点

    创建索引

    为表中某个列创建索引,要求该列是XML数据类型。

    ALTER TABLE Student
    ADD xml_test XML;--对Student表添加一个XML数据类型字段xml_test
    --对Student表的xml_test字段创建主XML索引,命名为学生信息表
    CREATE PRIMARY XML INDEX 学生信息表
    ON Student(xml_test)
    GO
    --对Student表的xml_test字段创建PATH辅助XML索引,记得写上主索引名
    CREATE XML INDEX 辅助学生信息表
    ON Student(xml_test)
    USING XML INDEX 学生信息表 FOR PATH
    GO
    

    注:辅助索引的命名不能与主索引相同。

    要获得相同的最终结果,可以改用 XMLQuery 函数。但如果将上一个示例中使用的 XQuery 表达式参数传递给 XMLQuery(如下所示):

    XQuery的应用领域

    修改和删除索引(ALTER INDEX 和 DROP INDEX)
    ALTER INDEX ALL ON Student--重建所有索引
    REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
    --删除索引
    DROP INDEX 学生信息表 ON Student
    GO
    

    注:删除主索引,与其相关的所有辅助索引也会被删除。因此上面语句中删除学生信息表索引后,辅助学生信息表索引也被删除了。

    SELECT XMLQuery('for $i in /PurchaseOrder
    where $i/User eq "EABEL"
    return 
    {$i/Reference}
    
    {fn:sum(for $j in $i/LineItems/LineItem/Part
    return ($j/@Quantity*$j/@UnitPrice))}
    
    '
    PASSING OBJECT_VALUE
    RETURNING CONTENT)
    FROM purchaseorder;

    在服务器端使用XQuery的优点

    OPENXML函数

    OPENXML是一个行集函数,用于检索XML文档。在试用OPENXML函数之前,一定要先用系统存储过程sp_xml_preparedocument分析文档,该存储过程在分析完XML文档后会返回一个句柄,使用OPENXML检索文档时要将该句柄作为参数传给OPENXML。
    示例15

    --定义两个变量@Student和@StudentInfo
    DECLARE @Student int
    DECLARE @StudentInfo xml
    --使用SET为@StudentInfo赋值
    SET @StudentInfo='
    <row>
    <姓名>祝红涛</姓名>
    <班级编号>2019382910</班级编号>
    <成绩>89</成绩>
    <籍贯>沈阳</籍贯>
    </row>
    '
    --使用系统存储过程sp_xml_preparedocument分析由@Student变量表示的XML文档,将分析得到的句柄赋值给@Student变量
    EXEC sp_xml_preparedocument @Student OUTPUT,@StudentInfo
    --在SELECT语句中使用OPENXML函数返回行集中的指定数据
    SELECT * FROM OPENXML(@Student,'/row',2)
    WITH(
    姓名 varchar(8),
    班级编号 varchar(10),
    成绩 int,
    籍贯 varchar(20)
    );
    

    结果如图所示
    图片 25
    在上述语句中,sp_xml_preparedocument存储过程语句用了2个参数,其中@Student是一个int型变量,该存储过程会将句柄存储在@Student变量中作为结果数据,@StudentInfo是一个XML类型的变量,存储了将要进行分析的XML文档。
    OPENXML函数的语句中,使用了3个参数,其中@Student代表已经经过sp_xml_preparedocument存储过程分析的文档的句柄,’/row’使用XPath模式提供了一个路径,代表要返回XML文档中该路径下的数据行,2是一个可选数据参数,表示将这些数据行以元素为中心映射。

    则 XQuery 表达式返回的空序列将与 purchaseorder 表联接,从而包含在查询总结果集中。实际上,这意味着输出将不仅包含为用户 EABEL 请求的订单生成的 OrderTotal 元素,而且还包含为 purchaseorder 表中存储的所有其他订单生成的空行(默认情况下,purchaseorder 表包含 132 行)。从结果集中排除空行的方法之一是在 SELECT 语句的 WHERE 子句中使用 existsNode SQL 函数,而不是在 XQuery 表达式中使用 WHERE 子句,如下所示:

    当使用XML Schemas时如何执行XQuery

    SELECT XMLQuery('for $i in /PurchaseOrder
    return 
    {$i/Reference}
    
    {fn:sum(for $j in $i/LineItems/LineItem/Part
    return ($j/@Quantity*$j/@UnitPrice))}
    
    '
    PASSING OBJECT_VALUE
    RETURNING CONTENT) AS ordertotal
    FROM purchaseorder
    WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[User = "EABEL"]') = 1;

    XQuery表达式的结构

    以上查询与本部分开头的 XMLTable 示例生成相同的输出。

    XPath 2.0表达式

    查询 Oracle XML DB 信息库中的 XML 数据

    FLWOR语句

    为访问 Oracle XML DB 信息库中存储的 XML 数据,Oracle XQuery 引入了 fn:doc 和 fn:collection XQuery 函数。使用 fn:doc,您可以查询 XML 信息库中存储的单个 XML 文档,而 fn:collection 使您可以访问同一信息库文件夹中存储的多个 XML 文档。

    For

    正如本文之前(参阅使用关系数据构建 XML部分)介绍的示例所演示,使用 fn:doc 非常简单直接。它获取表示信息库文件资源 (URI) 的字符串并返回该 URI 指向的文档。要了解 fn:collection XQuery 函数的作用,同一文件夹中至少应有两个信息库文件。如果已经运行了列表 1 中的代码,则已经创建了 /public/employees 信息库文件夹并在其中存储了 employees.xml 文件。因此,您将需要在该文件夹中至少再创建一个 XML 文件,然后才能试用 fn:collection。列表 2 中的 PL/SQL 代码基于 SCOTT/TIGER 演示数据库模式的 dept 和 emp 表存储的关系数据构建 XML,然后将生成的 XML 文档作为 acc_dept.xml 保存到 /public/employees 信息库文件夹。要运行列表 2 中的 PL/SQL 过程,请确保以 SCOTT/TIGER 的身份登录。

    Where

    列表 2:基于关系数据构建 XML 并将其保存到 XML 信息库

    order by

    DECLARE
    XMLdoc XMLType;
    BEGIN
    SELECT XMLQuery(
    'for $j in ora:view("SCOTT", "dept")/ROW
    where $j/DEPTNO = 10
    return ( 
    {$j/DEPTNO,
    $j/DNAME}
     {
    for $i in ora:view("SCOTT", "emp")/ROW
    where $i/DEPTNO = $j/DEPTNO
    return (
    
    {$i/EMPNO,
    $i/ENAME,
    $i/SAL}
    )} 
    
    )'
    RETURNING CONTENT) INTO XMLdoc FROM DUAL;
    IF(DBMS_XDB.CREATERESOURCE('/public/employees/acc_dept.xml', XMLdoc)) THEN
    DBMS_OUTPUT.PUT_LINE('Resource is created');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Cannot create resource');
    END IF;
    COMMIT;
    END;
    /

    return

    此时,/public/employees 信息库文件夹应包含两个文件:acc_dept.xml(由列表 2 中的 PL/SQL 代码生成)和 employees.xml 文件(由列表 1 中的代码生成)。由于这些 XML 文档存储在同一信息库文件夹中,因此可以使用 fn:collection 函数访问两个 XML 文档中存储的员工信息。然而,尽管这些 XML 文档均包含员工 XML 元素(这些元素实际上具有相同结构),但 XML 文档本身的结构迥然不同。在 employees.xml 中,文档根元素为 EMPLOYEES,而 acc_dept.xml 将 DEPARTMENT 用作根元素。要解决此问题,可以通过 XQuery 使用 XPath // 构造,从而导航到 XML 文档中的某个节点,而不必指定该节点的确切路径。以下示例演示了如何在 XQuery 表达式中使用 XPath // 构造:

    FLWOR表达式vs. XPath表达式

    SELECT XMLQuery(
    'for $i in fn:collection("/public/employees")//EMPLOYEE
    where $i/SAL >= 5000
    order by $i/ENAME
    return;
    $i'
    RETURNING CONTENT) FROM DUAL;

    XQuery的操作

    该构造应生成以下输出:

    数学运算符

    102
    De Haan
    17000
    
    
    7839
    KING
    5000
    
    
    100
    King
    24000
    
    
    101
    Kochhar
    17000

    比较运算符

    您可以看到,以上输出包含从 employees.xml 和 acc_dept.xml 中获取的员工 XML 元素,这些元素表示薪酬大于或等于 5,000 美元的员工。

    普通比较运算符

    将 XML 分解为关系数据

    数值比较运算符

    如果应用程序处理关系数据而非 XML,而您需要访问的数据以 XML 格式存储,则将 XML 分解为关系数据可能会非常有用。继续进行上一部分的示例,您可以使用 SQL 函数 XMLTable 将员工 XML 元素分解为虚拟表的单个列,如下所示:

    节点比较运算符

    SELECT emps.empno,emps.ename, emps.sal FROM 
    XMLTable(
    'for $i in fn:collection("/public/employees")//EMPLOYEE
    where $i/SAL >= 5000
    return;
    $i'
    COLUMNS empno NUMBER PATH '/EMPLOYEE/EMPNO',
    ename VARCHAR2(30) PATH '/EMPLOYEE/ENAME',
    sal NUMBER PATH '/EMPLOYEE/SAL') emps;

    节点顺序比较运算符

    该查询将生成以下输出:

    逻辑操作符

    EMPNO ENAME SAL
    ----- -------------- ----------
    7839 KING 5000
    100 King 24000
    101 Kochhar 17000
    102 De Haan 17000

    if-then-else语句

    查询外部数据源

    使用XQuery构造XML

    使用 XQuery,可以基于 XML 数据以及可以用 XML 表示的非 XML 数据生成 XML 文档,无论其位置如何:无论是存储在数据库中、置于网站上、即时创建还是存储在文件系统中。但要注意,Oracle XML DB 为针对数据库中存储的数据进行的 XML 操作提供了非常高的性能和可伸缩性。因此,如果您能够完全控制所处理的数据,则最好将它移动到数据库中。

    XQuery构造器 vs. FOR XML语句

    正如您从前面的示例中了解到的,在 Oracle XQuery 实施中,doc 和 collection XQuery 函数用于访问 Oracle XML DB 信息库中存储的 XML 文档。可以通过 XMLTable 和 XMLQuery SQL 函数中的 PASSING 子句动态绑定外部数据源。考虑以下示例。假设您的公司要为那些致力于 XQ 项目的员工支付奖金。因此,财务部发布了 empsbonus.xml 文件,其中包含有资格获得奖金的员工列表以及该列表中输入的每个员工的奖金数额。empsbonus.xml 文件可能如下所示:

    XQuery内置函数

    100
    1200
    
    
    101
    1000

    数据访问

    在实际情况中,以上的 XML 文件可能置于网站上(因此可以通过互联网获得)、以文件形式存储在本地文件系统中,或以文件资源形式存储在 Oracle XML DB 信息库中。就本示例而言,该文件位于网站上。为简单起见,可以在目录(Web 服务器在其中存储可从 Web 看到的文档)中创建一个员工文件夹,然后在该文件夹中插入 empsbonus.xml 文件,以便可以通过以下 URL 访问 empsbonus.xml 文件:

    字符串的处理

    http://localhost/employees/empsbonus.xml
    

    聚集函数

    接下来,假设您需要基于 empsbonus.xml 文档中存储的数据创建一个报表。在该报表中,您可能不但要包含列表中显示的奖金数额以及每个员工的员工 ID,还要包含他/她的全名。因此,可以首先使用以下查询生成一个新的 XML 文档(假设您以 HR/HR 的身份连接):

    上下文函数

    SELECT XMLQuery(
    'for $k in 1
    return (
     {for $i in ora:view("employees")/ROW,
    $j in $emps/EMPLOYEES/EMPLOYEE
    where $i/EMPLOYEE_ID = $j/EMPNO
    return (
    {xs:string($i/EMPLOYEE_ID)}
    {xs:string(fn:concat($i/FIRST_NAME, " ", $i/LAST_NAME))}
    {xs:integer($j/BONUS)}
    )} )'
    PASSING xmlparse (document httpuritype
    ('http://localhost/employees/empsbonus.xml').getCLOB()) as "emps"
    RETURNING CONTENT).getStringVal() as RESULT FROM DUAL;

    关系类型表达式

    以上查询是一个有关如何使用 XQuery 基于 XML 和非 XML 数据(以不同的方式从不同的数据源中检索)生成 XML 文档的示例。具体而言,使用 ora:view() 函数访问 HR 演示模式中的默认 employees 关系表,并使用 PASSING 子句中的 httpuritype() 函数借助于 HTTP 访问 empsbonus.xml 文档。然后,在 FLWOR 表达式的 return 子句中构建新的 XML 文档。最后,将获得以下 XML 文档:

    类型声明表达式

    
    100
    Steven King
    1200
    
    
    101
    Neena Kochhar
    1000
    

    如for语句中的xs:TYPE

    解决性能问题

    类型检查表达式

    正如您从前面的部分中了解到的,XQuery 是一种用于查询 Oracle 数据库存储的 XML 内容的高效方法 - 无论您是处理本地存储的 XMLType 数据还是查询基于关系数据构建的 XML 视图。但根据对数据使用的存储类型的不同,XQuery 表达式的执行性能可能迥然不同。尤其是,Oracle XML DB 可以优化基于由 ora:view 函数创建的 SQL/XML 视图而构建的 XQuery 表达式。对于 XMLType 表或列中存储的 XML 数据,只能对使用结构化(对象-关系)存储技术存储的基于 XML 模式的 XMLType 数据进行 XQuery 优化。

    xs:TYPE实例

    所选择的存储模型并非是影响 XQuery 表达式执行性能的唯一因素。在某些情况下,XQuery 表达式本身的结构也可能导致性能问题。要监控 XQuery 表达式的性能,可以打印并检查关联的 EXPLAIN PLAN。在 SQL*Plus 中,只需设置 AUTOTRACE 系统变量,即可打印 SQL 优化程序使用的执行路径。但要执行该操作,请确保创建 PLUSTRACE 角色,然后将其授予连接到数据库所使用的用户。有关如何执行此操作的信息,请参阅 Oracle 数据库 10g 第 2 版 (10.2) 文档中《SQLPlus 用户指南和参考》一书中的“调整 SQLPlus”一章。以下示例演示了如何通过检查 EXPLAIN PLAN 生成的执行计划来获得好处。假设您已经将 PLUSTRACE 角色授予默认用户 OE,以 OE/OE 的身份登录并运行以下查询:

    类型转换函数

    SET AUTOTRACE ON EXPLAIN
    SELECT count(*)
    FROM oe.purchaseorder, XMLTable(
    'for $i in /PurchaseOrder/User
    where $i = "CJOHNSON"
    return $i'
    PASSING OBJECT_VALUE) ptab;

    隐式类型转换

    这将生成以下输出:

    显式类型转换

    COUNT(*)
    ----------
    9
    Execution Plan
    ---------------------------------------------
    Plan hash value: 4046110317
    --------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 226 | 29 (0) | 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 226 | | |
    | 2 | NESTED LOOPS | | 10782 | 2379K | 29 (0) | 00:00:01 |
    |* 3 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 226 | 5 (0) | 00:00:01 |
    | 4 | COLLECTION ITERATOR P| XMLSEQUENCEFROMX| | | | |
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype('...

    值类型构造器

    您可能对为以上查询生成的执行计划并不满意。尤其是,所处理的行数可能非常大。由于 SQL 调整的主要目标是避免访问对结果没有任何影响的行,因此可能要继续调整查询以优化性能。对查询中包含的 XPath 表达式进行重新建模后,可以再次重试它,如下所示:

    cast as xs:TYPE ?操作符

    SELECT count(*)
    FROM oe.purchaseorder, XMLTable(
    'for $i in /PurchaseOrder
    where $i/User = "CJOHNSON"
    return $i/User'
    PASSING OBJECT_VALUE) ptab;
    这次,输出应如下所示: 
    COUNT(*)
    ----------
    9
    Execution Plan
    ---------------------------------------------------
    Plan hash value: 3411896580
    ---------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 29 | 7 (0) | 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 29 | | |
    | 2 | NESTED LOOPS | | 1 | 29 | 7 (0) | 00:00:01 |
    | 3 | FAST DUAL | | 1 | | 2 (0) | 00:00:01 |
    |* 4 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 29 | 5 (0) | 00:00:01 |
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    4 - filter("PURCHASEORDER"."SYS_NC00022$"='CJOHNSON' AND
    SYS_CHECKACL("ACLOID","OWNERID",xmltype('...

    访问关系性的列和变量

    您可以看到,以上显示的查询生成相同的最终结果,但它们的执行计划并不相同。查看最后一个示例中的 XQuery 表达式,您可能会注意到它迭代顶层 PurchaseOrder 元素,其中的每个 PurchaseOrder 元素都表示基于 PurchaseOrder XMLType 模式的表中的一行。这意味着实际上重写 XQuery 表达式,以迭带基础对象表(用于存储分解的 PurchaseOrder 文档)中的行。与查询要迭代不表示基础表中的单个行的 XML 元素相比,该方法的性能更好一些。

    不支持的特性和工作中的解决方案

    但在某些情况下,很难发现 XQuery 表达式的哪个构造将使某些查询的性能更好。这就是为什么最好在开发阶段使用调整工具的原因。

    最佳实践和指导方针

    将动态变量绑定到 XQuery 表达式

    XML数据的修改

    另一种可以显著提高 XQuery 表达式执行性能的技术是使用绑定动态变量。使用绑定变量(而不是将变量串联为字符串)可以使 Oracle 重用 SQL 语句,从而减少分析开销并显著提高应用程序的性能。可以在 XMLQuery 和 XMLTable SQL 函数中使用 PASSING 子句将动态变量绑定到 XQuery 表达式。该技术使您可以根据客户端代码中计算的参数动态生成 XML。列表 3 中的示例演示了如何在从 PHP 脚本执行的 XQuery 查询中使用绑定变量。

    插入操作

    列表 3:使用绑定变量

    删除操作符

    //File:BindVars.php
    $user = 'hr';
    $pswd = 'hr';
    $db ='(DESCRIPTION=
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
    )
    (CONNECT_DATA=(SID=orclR2)(SERVER=DEDICATED))
    )';
    $empno=100;
    $conn = oci_connect($user, $pswd, $db);
    $sql = 'SELECT XMLQuery('."'".'for $i in ora:view("employees")/ROW
    where $i/EMPLOYEE_ID = $empno
    return (
    {$i/EMPLOYEE_ID,
    $i/EMAIL,
    $i/JOB_ID}
    )'."'".'PASSING XMLElement("empno", :empno) AS "empno"
    RETURNING CONTENT).GetStringVal() AS RESULT FROM DUAL';
    $query = oci_parse($conn, $sql);
    oci_bind_by_name($query, ":empno", $empno, 3);
    oci_execute($query);
    oci_fetch($query);
    $str = oci_result($query, 'RESULT');
    print $str;
    ?>

    Update操作符

    列表 3 中显示的脚本应生成以下输出(注意,浏览器中可能不会显示标记):

    XQuery应用场景

    100
    SKING
    AD_PRES

    场景1:性能评价系统

    XQuery 与 XSLT

    场景2:病例系统

    尽管 Oracle 在 Oracle XML DB 中提供了一个自带 XSLT 处理器,但在很多情况下(尤其是在处理大型文档时),XQuery 对于构建 XML 更高效。此外,XQuery 表达式通常比为同一作业设计的 XSLT 样式表更具可读性,并且更清楚。与 XSLT 一样,XQuery 不但可用于将一个 XML 文档转换为另一个 XML 文档,而且还可用于将 XML 转换为另一种基于文本的格式,如 HTML 或 WML。

    场景3:资产管理系统

    在本文前面的查询 XMLType 数据部分中,您看到了一个有关使用 XQuery 将一个 XML 文档转换为另一个 XML 文档的示例。具体而言,该示例使用 XQuery 表达式计算示例数据库模式 OE 的 purchaseorder 表中存储的订单的订单总计,然后为处理的每个订单生成了一个 OrderTotal XML 元素。实际上,您可以使用 XSLT 执行相同操作。为此,您首先需要创建一个应用于 PurchaseOrder XML 文档的 XSLT 样式表,以生成相应的 OrderTotal 元素。对于此示例,可以使用列表 4 中所示的 XSLT 样式表。

    结论

    列表 4:使用 XSLT 计算小计总和 (Quantity * UnitPrice)

    导言

    http://www.w3.org/1999/XSL/Transform" version="1.0">
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    XML是被用来作为一种文档格式而发展起来的。然而,XML所具有的其它特征,比如可扩展性、支持国际标准、表示结构化和半结构化数据的能力,以及人和机器对其简单的可读性,使得它成为一种广泛应用的、平台无关的数据表示格式。由于XML获得了广泛的认可,因此很多用户都用XML来解决复杂的商业问题,例如那些涉及到数据集成的问题。有许多案例都推荐将信息直接存储到XML文档,这比将信息先存到数据表中然后再将这些信息组成XML消息要有效的多。要查看更多关于这些案例的信息,可以查阅“微软SQLserver2005 XML最佳实践”白皮书中相关文档。XML在存储文档和半结构化数据表示方面的应用已经让XML发展成为一种可以在服务器端存储简单数据管理的数据存储格式。

    为方便起见,您可能需要将此 XSL 样式表保存在数据库中,然后再开始使用它。例如,您可以将样式表作为文件资源保存在 Oracle XML DB 信息库中。执行该操作的方法之一是将样式表作为文件保存到本地文件系统中,然后使用以下某个互联网协议将它移动到 XML 信息库:FTP、HTTP 或 WebDAV。假设您已经将列表 4 中的 XSLT 样式表作为 orderTotal.xsl 保存在 /public 信息库文件夹中,现在可以按以下示例所示将它用作 XMLTransform SQL 函数的参数(假设您以 OE/OE 的身份登录):

     

    SELECT XMLTRANSFORM(OBJECT_VALUE,
    xdbUriType('/public/orderTotal.xsl').getXML()).GetStringVal() AS RESULT FROM
    purchaseorder WHERE existsNode(OBJECT_VALUE, 
    '/PurchaseOrder[User = "EABEL"]') = 1;

    可是这种发展也导致了一个问题-从XML数据中提取出来的信息要作为BLOBs类型存储到关系型数据库中,需要一种查询语言来提取XML所表达的信息,并使其符合关系型数据库中的要求。Microsoft  SQL Server  2000提供了OpenXML,它可以被用来查询,从被设计时,就满足了从XML数据映射到关系型数据格式的要求,但它并没有完全支持XML数据模型(见表1)。关系数据模型和XML数据模型在很多方面都不一样。下表简单列举了两种数据模型的主要区别。

    以上查询将处理用户 EABEL 请求的所有订单(即存储在 XMLType 的默认 PurchaseOrder 表中的订单)并将生成与查询 XMLType 数据部分中的 XQuery 查询相同的输出。

     

    将列表 4 中的 orderTotal XSLT 样式表与查询 XMLType 数据部分中的示例使用的 XQuery 表达式进行比较,您可能会注意到,XQuery 方法要比 XSLT 方法更具吸引力。至少在使用 XQuery 时,您只需编写很少的代码即可获得相同的最终结果。

    表1   关系型数据模型与XML数据模型之间的区别

    查询 RSS 新闻提供

    图片 26

    由于 RSS 新闻提供本质上是一个托管的 XML 文件(RSS 新闻阅读器从中获取头条新闻或其他内容),因此可以像处理任何其他可以通过 Web 获得的 XML 文档那样来处理它。正如您在本文前面的查询外部数据源部分中所见,可以使用 XQuery 查询任何可以通过 URL 访问的 XML。您通过 XMLTable 和 XMLQuery SQL 函数中的 PASSING 子句动态绑定所有外部 XML 数据源。以下是一个查询 RSS 新闻提供的 XQuery 示例:

     

    SELECT XMLQuery(
    'for $i in $h//channel
    return;
    
    {$i/lastBuildDate}
    
    {for $j in $h//item
    where ora:contains($j, "PHP")
    return  {($j/title, $j/link)}}
    
    '
    PASSING xmlparse (document httpuritype
    ('http://www.oracle.com/technology/syndication/rss_otn_news.xml').getCLOB()) as "h"
    RETURNING CONTENT).getStringVal() as RESULT FROM DUAL;

     

    该 XQuery 应生成一个 XML 文档,其中包含 Oracle 技术网 (OTN) 最近发布的与 PHP 技术相关的头条新闻列表。所生成的 XML 文档可能如下所示:

    不断增加处理越来越多样化的结构数据的需求和数据间隐含约束的必要性是扩展关系型数据模型以支持XML文档存储的最重要的两个原因。另外,SQL语言在处理半结构化或者标记信息时的局限性也促使了XQuery语言的发展。XQuery语言在设计的时候已经考虑到XML数据的特性和处理XML数据的相关问题。

    Tue, 01 Nov 2005 19:37:42 GMT
    
    
    http://www.oracle.com/technology/xe
    
    
    http://www.oracle.com/technology/pub/articles/oracle_php_cookbook
    
    
    http://www.oracle.com/technology/tech/php/zendcore/index.html
    
    

    SQL Server 2005通过内置XML数据类型支持XML数据的本地化存储。XQuery 1.0版本是经World Wide Web Consortium (W3C) XML查询工作组定义,基于XML数据的公式化查询语言。XQuery,像SQL一样,是一种声明性的查询语言,就如同我们在下面的文章中将会看到的一样,能通过基本的SQL和XPath知识来轻松地理解它。

    但在开发实际应用程序时,您将很可能需要 XQuery 表达式直接生成 HTML 标记,而不是仅仅生成一个如上所示的 XML 文档。这样,您便可以构建一个更灵活、可维护性更高的应用程序,原因是在这种情况下,所有 RSS 处理(从提取必要的数据到将它包装在 HTML 标记中)都将转移到数据库。这使您不必编写负责 RSS 处理的应用程序代码。实际上这意味着您不必在诸如 RSS 新闻提供的结构已经更改的情况下修改应用程序代码。相反,您只需修改用于 RSS 处理的 XQuery 表达式。

    本文基于XQuery 1.0在SQL Server 2005中的实践,同时也基于XQuery 1.0 2004年的工作草图。本文的第一部分提出了新的XML数据类型的总述以及它的相关特点,接下来的部分,分别地介绍XQuery的多样操作,XQuery的内置函数,关系型表达式,以及SQL Server 2005不支持的部分。最后,介绍最近的最佳应用和指导,XML数据的修正以及XQuery的应用事例的信息。

    总结

    引用:

    您已经在本文了解到,XQuery 是一个综合的查询语言,它提供了一种用于查询、构建和转换 XML 数据的高效方法。尽管 Oracle XQuery 实施使您可以操作任何可以用 XML 表示的数据(无论它存储在数据库中、位于网站上还是存储在文件系统中),但将处理的数据移动到数据库中始终是一个不错的主意。对于数据库中存储的数据,Oracle XML DB(对 XPath 重写使用同一机制)只能显著优化处理那些基于以下数据构建的 XQuery 表达式:这些数据包括关系数据、对象-关系数据或使用结构化(对象-关系)存储技术存储的基于 XML 模式的 XMLType 数据。

    SQL Server 2005中的XML数据类型

    (责任编辑:铭铭)

    SQL Server 2005引入的新XML数据类型使得用户具有在数据库中存储XML文档和段落的能力。一个XML数据类型可以用来创建列,用来创建存储过程或者存储函数中的参数,或用来创建变量。另外,用户可以关联一个XML类型的列和一个XML Schema集来创建一个新的类型化的XML列。集合中的XML Schema被用来验证和类型化XML实例。

    原文:Oracle XQuery查询、构建和转换XML 返回数据库首页

     

    类型化vs.非类型化的XML数据类型

    XML数据类型与XML schema集相关联,为XML实例增加了Schema的强制性约束。如果XML数据被关联到一个XML Schema集合,它就被称为类型化的XML。否则,就叫做非类型化的XML。

     

    SQL Server 2005中XML数据类型实现了ISO SQL-2003标准化XML数据类型。它不仅能够存储结构良好的XML1.0文档,也能存储根节点为文本的所谓的XML内容段落,也能存储包含任意数目根元素的内容。针对具有良好格式数据的检查已经完成,这些检查不必把XML数据类型绑定到XML Schemas。那些没有良好格式的数据不能被检查。

     

    当Schema是不可知的时候,非类型化的XML就很有用。当Schema可知,但它的变化非常快以至于很难保持的时候,或者当存在多个Schema,最后的绑定取决于外部的需求时,非类型化的XML也很有用。另外,当XML Schema中包含数据库引擎不支持的XML Schema结构时,非类型化的XML也很有用。在这些情况下,你可以使用在公共语言运行时(CLR)用户自定义功能中的System.XML验证器来提供有效性验证。

     

    如果在XML Schemas集中,你有XML Schemas来描述你的XML数据,你能够通过关联XML Schema集和XML列用来提供类型化的XML数据。XML Schema被用来验证数据的有效性;在编译查询或者数据编辑语句期间执行比非类型化XML更加精确的类型检查;或者被用来优化存储和查询的处理效率。

     

    类型化的列,参数和变量能够存储XML文档(Document)或者内容段落(Content),在声明的时候,你可以通过开关指定你存储的类型(文档或者内容,默认的是内容)。另外,你必须同时提供一个XML Schema集。如果每个XML实例只有一个根元素,那么指定为文档类型,否则指定为内容类型。在静态类型推断期间,XQuery编译器使用文档(Document)标志信息来推断单个的根元素。

    引用:

    XML数据类型的方法

    XML数据类型支持五种方法来操作XML实例。XML数据类型的方法描述如下:

    query() 方法携带一个对若干个XML节点求值的XQuery表达式,允许用户查询一个XML文档中的段落。这个方法的返回值一个非类型化XML文件类型的值。

     

    value() 方法是用来从一个XML文档数据值中提取关系型数值。这个方法携带一个用来识别单个XML节点的XQuery表达式并返回期望得到的SQL类型。XML节点的返回值被转换为指定的SQL类型。

     

    exist()方法允许用户在XML文档中执行查询以确认一个XQuery表达式的结果是否为空。当XQuery表达式返回一个非空的结果时,这个方法的返回值为1;当表达式返回空时,方法返回值为0;当XML实例本身为NULL时,就返回NULL值。

     

    使用XML数据类型的nodes()方法可以较容易的把一个XML文档分解为关系型数据。nodes()方法接受一个XQuery表达式,并返回一个行集(Rowset),行集中的每一行都描述一个由查询表达式所标识出来的上下文节点。XML数据类型的其他方法,如query(), value(),exist(),和 nodes(),都能调用nodes()方法返回的上下文节点。

    modify()方法能用来对一个XML文档的内容进行修改。它支持XML DML语句,从而实现在一个XML实例中插入、更新或者删除一个或多个节点。当它遇到一个NULL值时,它会报错。

    详细内容,查阅“微软SQLserver2005 XML最佳实践”白皮书。

    XQuery入门

    XQuery是一种用于XML查询的新语言,支持基于XPath2.0的数据浏览和采集。本章节为我们初步展示了XQuery语言各方面的内容,比如XQuery和XPath之间的关系,XQuery的优点,XQuery的应用领域,XQuery中XML Schema的规则等等。

     

    XPath2.0简介

    XPath1.0,是由W3C工作组定义,用于在单个XML文档中定位节点的语言。XPath1.0使用基于路径的规则来标识XML文档中的节点。同时它定义了XSLT 1.0和XPointer的核心规则。XPath 1.0有处理字符串、布尔值和浮点数的内置函数。它定义了根据设置的过滤标准来过滤节点的语法。XPath 1.0正在升级为XPath 2.0以支持更多的系统类型,提供更多的功能。XQuery 1.0基于对XPath 2.0,增加了排序、重装、构造功能,而且实现了XPath2.0未能实现的数据浏览和过滤方面的性能。

     

    XQuery简介

    XQuery是一个可以定义、类型化、函数化的语言,是XML查询组专为从XML格式中查询数据而设计的语言。XQuery具有与XML标准系列中XPath 2.0和XSLT 2.0一样的数据模型和XML Schema类型系统。设计XQuery用于处理非类型化XML文档(不包括关联数据的schema),类型化XML schema,或者两者的混合体。如同上文提到的,XQuery 1.0基本上是XPath2.0的扩展集。XQuery 1.0除了拥有XPath2.0的特点外,它还拥有下面的性能:

    •        在FLWOR语句中增加了order by语句,它可以对文档数据进行重新排序。

    •        在FLWOR语句中增加了let语句,它能够指定表达式的结果以实现更多的应用(SQL Server 2005中不支持)。

    •        在查询语句的嵌缀部分,可以指定静态的上下文节点(比如命名空间的绑定前缀namespace prefix bindings)。

    •        提供构造新节点的功能。

    •        允许用户自定义函数(SQL Server 2005中不支持)。

    •        能够创建modules/libraries(SQL Server 2005中不支持)。

    XQuery的优点

    •        当前的SQL和XPath知识是很容易学习的。

    •        相比XSLT的查询语句,XQuery查询语句代码更简洁。

    •        当XML数据是类型化的,那么XQuery是一个强类型语言,它能够通过避免非法的类型转换以及确认类型是否可以在查询操作中使用,来提高查询语句的执行效率。

    •        XQuery能当作弱类型语言使用,为非类型化数据提供更强的功能。SQL Server 2005提供了同时支持强类型和弱类型两种关系的静态类型。

    •        因为XQuery执行查询需要的代码比XSLT少,所以它的执行效率也高。

    •        XQuery正在成为W3C工作组的推荐语言,同时它也将会被主流的数据库提供商所支持。

    在本文中,关于在编写XQuery1.0语言时需要特别注意的地方:

    •        XQuery规范是基于当前开发环境进行阐述,将来可能会发生变化。而SQL Server 2005是W3C工作草图所实现的稳定部分,不会发生变化。

     

    XQuery的应用领域

    一般,XQuery的应用领域分类如下:

    •        查询和分析数据:XQuery在查询大容量数据时表现出色,而且能够过滤、分类、排序以及转换需要的信息。典型的XML文档查询的应用包括描述半结构化信息,定义name-value包,分析日志,处理日志以及监控应用的日志来查找潜在的应用错误和安全方面的问题等等。

    •        XQuery的集成应用:当团队开始摒弃自己私有的集成应用方法,而开始采用标准的以集成应用为基础的方法时候,满足把单个应用中内部定义使用的数据转换为标准的可以格式化转换的数据的需求就成为头等重要的问题。因为XQuery能够构造并转换XML数据,所以XQuery就满足了这个需求。在集成应用领域,一个典型的XQuery应用是把本地使用的XML数据库/关系型数据资源的词汇表,翻译为另外一个应用者的本地XML数据库/关系型格式数据语言。

     

    在服务器端使用XQuery的优点

    对比起在客户端处理XML过程,在服务器端使用XQuery来处理XML过程要具备更多的优点。其中一些优点可以总结如下:

    •        减少网络负载:在服务器端处理XML数据,只把结果传递到客户端,减少了网络负载。

    •        更加安全:只有当使用客户端XML进程时才把客户端需要的数据传递到客户端,避免在网络上传输完整数据而带来的风险。

    •        更易维护:在服务器段处理XML能使得浏览器独立于客户端代码,这能够更容易的维护客户端。

    •        性能的改进:在服务器端使用XQuery写的查询语句可以使用SQL查询器进行优化。这样优化处理的性能要高于在客户端重新获得数据并进行数据过滤。此外,还可以通过为XML数据类型的列创建索引来得到更强大的性能。

     

    当使用XML Schemas时如何执行XQuery

    关联一个XML数据类型的XML schema集能被以下的关系引擎使用:

    •        在插入操作中来实例化XML。

    •        在修改操作中来实例化XML。

    •        在进行最早的静态类型错误检查和改进查询性能时,XML schema中的类型信息被用来确定最佳的查询计划和避免很多运行时的检错。

    •        XML schema中描述的类型信息被SQL Server用来优化存储。

    引用:

    XQuery表达式的结构

    SQL Server 2005中的一个XQuery表达式包括两个部分—前缀(prolog)和主体(body)。前缀能逐个声明包含的命名空间。命名空间的声明可以通过映射前缀和命名空间的URI,使你能够用前缀来替代查询体中的命名空间的URI。通过默认声明命名空间(declare default namespace)的声明,你不用绑定元素名称的默认命名空间就可以引用元素的名称。

     

    XQuery表达式的主体包含了定义查询结果的查询表达式。比如,它可以是一个FLWOR表达式(参见本文章节的“FLWOR语句”),一个XPath 2.0 表达式(参见本文章节的“XPath 2.0表达式”),或者另外一个XQuery表达式比如一个构造或算术表达式。

     

    实例:在XQuery头中声明默认的命名空间

    下面是从所有职员中查找一个JobCandidateID等于3的应聘者。查询中只定义了一个默认命名空间,而没有使用任何一个命名空间前缀。

    SELECT Resume.query('

            declare default namespace  "";

            /Resume/Employment

    ') as Result

    FROM [HumanResources].[JobCandidate]

    WHERE JobCandidateID = 3

     

    实例:使用“WITH XMLNAMESPACES”语句声明命名空间

    SQL Server同样支持 SQL-2003的标准扩展,允许用户在SQL的每一个SQL查询要素上绑定XML命名空间,这样就可以避免重复声明同样的XML数据类型的方法。下面的查询显示了前面查询实例修改后的版本。这个查询使用WITH XMLNAMESPACES 语句声明了一个命名空间。

    WITH XMLNAMESPACES( '' AS  "RES")

    SELECT Resume.query('

            /RES:Resume/RES:Employment

    ') as Result

    FROM [HumanResources].[JobCandidate]

    WHERE JobCandidateID = 3

     

    XPath 2.0表达式

    XQuery使用XPath2.0表达式查找一个文档中的节点,在一个文档中移动节点或者在文档间移动节点位置。定义移动路径的XPath是一个彼此分离的步骤组成的有序队列。每一个步骤包含一个axis,一个node test和多个步骤判断。

     

    Axis说明了移动的方向,和上下文节点的关系。SQL Server2005中支持的axes是child, descendant, parent, attribute, self 以及descendant-or-self。

     

    一个node test说明了步骤选择的这些节点所必需满足的条件。节点的条件可以基于节点名称或者节点类型来说明。

     

    步骤判断可以用动作(predicates)或者重新引用(dereference)。一个动作就充当一个在相等框架下定义的一个节点队列中的节点过滤器;一个重新引用反映出一个节点队列中各节点的属性以及元素是否与参照节点的一致。作为重新引用的输入,该节点队列必须包含IDREF或者IDREFS类型的元素或者属性。重新引用可以产生一个新队列,其各节点ID类型的属性值都匹配取自输入队列中元素和属性的IDREF值。

     

    XPath表达式的步骤是可以关联求值的。每个步骤执行时都为下一步设置了赋值的上下文项。Path表达式中的一个上下文项就是一个节点,它被选择作为XPath表达式中每个步骤执行的返回值。根据前一步所获得的上下文节点能够关联求得当前的步骤。XPath表达式执行完所有步骤的返回结果就将是一个根据path表达式排列的文档中节点的有序队列。

     

    下面的表达式用AdventureWorks数据库中的表[HumanResources].[JobCandidate]来举例说明了path表达式的概念。下面的path表达式选择了address类型节点中值为Home的节点。

    //child::ns:Addr.Type[.="Home"]/parent::node()

    Path表达式:

    •        child 是axis的定义。

    •        :: 是 axis 的分隔符。

    •        ns 是命名空间前缀。

    •        Addr.Type 是 node test。

    •        [.=”Home”] 是一个指向上下文节点的动词表达式。

    XQuery同样支持缩写语法来说明axis。下面的表格列举了axis以及相应的缩写语法。

    表 2  axes缩写语法

    图片 27

     

     

    示例:从历史职业中选择工作单位的名称

    下面所示的XPath表达式选择了Emp.OrgName文本类型的,同时也是Resume/Employment子节点的节点。在这里,text()用来选择Emp.OrgName元素文本类型的子节点:

    /Resume/Employment/Emp.OrgName/text()

    引用:

    FLWOR语句

    FLWOR语句组成了XQuery表达式的主体,它与SQL的SELECT语句很相似。FLWOR(发音同“flower”)是FOR, LET, WHERE, ORDER BY, RETURN的缩写。XQuery的FLWOR表达式可以进行重复声明、变量绑定、过滤、排序以及返回结果的操作。SQL Server 2005中支持FOR, WHERE, ORDER BY和 RETURN:

    For

    FLWOR表达式中的for语句支持用户定义一个变量来遍历某个输入序列。该输入序列可以使用XPath表达式、原子值序列、分隔符序列或结构化函数。因此,这里的for语句与SQL SELECT FROM语句类似,但与编程语言中的“for”语句不同。

     

    绑定变量同样可以使用for语句声明。

     

    示例:使用for语句从简历中检索所有的家庭地址

    下面的查询得出了JobCandidateID等于3的应聘者的address节点值等于Home的元素。

    SELECT Resume.query('

            declare namespace RES="";

            for $A in /RES:Resume/RES:Address/RES:Addr.Type[.="Home"]/..

            return 

            $A 

    ') as Result

    FROM [HumanResources].[JobCandidate]

    WHERE JobCandidateID = 3

    Where 

    FLWOR表达式中使用Where语句可以筛选一个迭代的结果。

     

    示例:使用where语句选择所有的家庭地址

    下面的查询得出了JobCandidateID等于3的应聘者的address节点值等于Home的元素。

    SELECT Resume.query('

            declare namespace RES="";

            for $A in /RES:Resume/RES:Address

            where $A/RES:Addr.Type[.="Home"]

            return 

            $A 

    ') as Result

    FROM [HumanResources].[JobCandidate]

    WHERE JobCandidateID = 3

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:构建和转换XML,在SQLServer中的使用

    关键词:

上一篇:管理实战,五年数据库之路的觉悟

下一篇:没有了