上手DB2的xml(1)

最新推荐文章于 2019-01-30 16:03:05 发布

水墨胭脂

最新推荐文章于 2019-01-30 16:03:05 发布

阅读量2.4k

点赞数

分类专栏： DB2 文章标签： db2 xml

本文链接：https://blog.youkuaiyun.com/nayanminxing/article/details/70229020

版权

DB2 专栏收录该内容

25 篇文章

订阅专栏

本文是作者接触DB2 pureXML的初步体验，详细介绍了如何在DB2中存储、查询和更新XML数据。文章涵盖pureXML概述、XQuery和XPath的使用，以及XML数据的插入、更新和删除操作。通过示例展示了如何在SQL和XQuery中处理XML文档，解答了如何在XML中实现类似json->key的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

接触DB2的第12天，因为工作任务的需要，开始接触XML的一些东西了，之前看的XML的一些百度相关的东西大致知道了XML是怎么样一个东西，其实因为json永久了蛮嫌弃xml的，然后知乎上说xml是面向机器的，对于机器来说结构很好，其实我觉得json就蛮好的，先来接触一下xml再来说二者的是非吧。
那我的需求是表中有xml字段，xml字段中有节点，那如果是json的话，我可以使用字段名->’键名’ 来获取对应的值，然后有个函数json_each()什么的，可以把字段名转换为一个表，那如果是xml呢，上述两个操作应该怎么做？

下载了DB2V10.5的中文文档，重点参考其中的DB2pureXML和DB2XQueryRef两个部分。

pureXML概述

pureXML功能部件语序您将格式良好的XML文档存储在具有XML数据类型的数据库表列中，通过将xml数据存储在xml列中，数据可保持其本机分层结构形式，而不是将其作为文本存储或映射为其他数据模型。
因为pureXML数据存储已完全集成。所以可利用现有DB2数据库服务器功能来访问和管理存储的XML数据。

查询和更新的方法

XQuery
- XQuery是用于解释、检索和修改查询XML数据的通用语言。DB2数据库服务器允许直接调用*XQuery或从SQL调用XQuery。因为XML数据存储在DB2表和视图中，所以提供了一些函数，用于通过直接命名表或视图或通过指定SQL查询从指定的表和视图中抽取XML数据*。XQuery支持各种用于处理XML数据、更新元素和属性之类的XML对象和构造新XML对象的表达式。XQuery的编程接口提供了类似SQL的功能，用于执行查询并检索结果。
SQL语句和SQL/XML函数
- 许多SQL语句支持XML数据类型。这是的您能够对XML数据执行许多常见数据库操作，例如，创建具有XML列的表、将XML列添加至现有表，创建基于XML列的索引、对具有XML列的表创建触发器以及插入、更新或删除XML文档。DB2数据库服务器支持的一组SQL/XML函数、表达式和规范利用XML数据类型。
- 可以从SQL查询中调用XQuery。在这种情况下，SQL查询可以将数据以绑定的变量的形式传递至XQuery。
- 当使用SQL全查询来查询XML数据时，将在列级别进行查询，因此查询将返回整个XML文档，如果要返回ＸＭＬ的片段，必须使用XQuery。

XQuery和XPath数据模型

XQuery表达式对XQuery和XPath数据模型（XDM）的实例进行运算并返回数据模型的实例。
XDM是对一个或多个XML文档或片段的抽象表示。数据模型会定义XQuery中的表达式的允许值，包括中间计算期间使用的值。

节点层次结构

文档节点D
元素节点E
属性节点A
文本节点T

pureXML教程

1.创建测试库和表

db2 => create database xmltut
DB20000I  CREATE DATABASE 命令成功完成。
db2 => connect to xmltut

   数据库连接信息

 数据库服务器         = DB2/NT64 11.1.1.1
 SQL 授权标识         = THINKPAD
 本地数据库别名       = XMLTUT

db2 => create table customer (cid bigint not null primary key,info xml)
DB20000I  SQL 命令成功完成。

2.创建基于XML数据的索引

create index cust_cid_xmlidx ON customer generate key using xmlPATTERN \
'declare default element namespace "http://posample.org";/customerinfo/@Cid'
AS　SQL DOUBLE

此语句将对customer表的info列中的元素的cid属性值建立索引（@后面是属性值）。缺省情况下，对XML数据建立索引之后，如果未能将此XML数据转换为指定的数据类型SQL DOUBLE,那么不会创建索引条目，也不会返回错误。
您指定的XML模式区分大小写。例如，如果XML文档中包含cid属性而不是Cid属性，那么那些文档与此索引将不匹配。

3.插入XML列

insert into thinkpad.Customer(cid,info) values(1000,
'<customerinfo xmlns="http://posample.org" Cid="1000">
    <name>Kathy Smith</name>
    <addr country="Canada">
        <street>5 Rosewood</street>
        <city>Toronto</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>M6w 1E6</pcode-zip>
    </addr>
    <phone type="work">416-555-1358</phone>
</customerinfo>')

'<customerinfo xmlns="http://posample.org" Cid="1002">
    <name>Jim Noodle</name>
    <addr country="Canada">
        <street>25 EastCreek</street>
        <city>Markham</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>N9C 3T6</pcode-zip>
    </addr>
    <phone type="work">905-555-7258</phone>
</customerinfo>')

insert into thinkpad.Customer(cid,info) values(1003,
'<customerinfo xmlns="http://posample.org" Cid="1003">
    <name>Robert Shoemarker</name>
    <addr country="Canada">
        <street>1596 Baseline</street>
        <city>Aurora</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>N8X 7F8</pcode-zip>
    </addr>
    <phone type="work">905-555-2937</phone>
</customerinfo>')

4.更新XML文档

pg里面json更新很方便，json_set或者||符号可以很容易的进行更新，不需要复制大段的原文档。那么XML的更新又是怎么做的呢？

* 使用SQL进行更新*

 update thinkpad.customer set info=
'<customerinfo xmlns="http://posample.org" Cid="1002">
    <name>Jim Noodle</name>
    <addr country="Canada">
        <street>1150 Maple Driver</street>
        <city>Newtown</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>Z9Z 2P2</pcode-zip>
    </addr>
    <phone type="work">905-555-7258</phone>
</customerinfo>'
where XMLEXISTS(
'declare default element namespace "http://posample.org";$doc/customerinfo[@Cid = 1002]'
passing INFO as "doc")

XMLEXISTS谓词确保仅替换包含属性Cid=“1002”的文档。
这种更新就是全部更新，需要把全部的文档取出进行更新，我喜欢那种只更新相应元素的方式。

使用XQuery更新表达式进行更新
- 如果您将UPDATE语句与XQuery更新表达式配合使用、那么可以更新现有XML文档的某些部分。

update thinkpad.customer set info =
 XMLQUERY('  declare default element namespace "http://posample.org";
 transform
 copy $mycust := $cust
 modify 
    do replace $mycust/customerinfo/addr with
    <addr country="Canada">
        <street>25 EastCreek</street>
        <city>Markham</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>N9C 3T6</pcode-zip>
    </addr>
 return $mycust'
 passing INFO as "cust")
where CID = 1002

可以使用“$字段名”的形式直接饮用变量了。

update thinkpad.customer set info =
 XMLQUERY('  declare default element namespace "http://posample.org";
 transform
 copy $mycust := $INFO
 modify 
    do replace $mycust/customerinfo/addr with
    <addr country="Canada">
        <street>1150 Maple Driver</street>
        <city>Newtown</city>
        <prov-state>Ontario</prov-state>
        <pcode-zip>Z9Z 2P2</pcode-zip>
    </addr>
 return $mycust')
where CID = 1002

5.删除XML文档的某些部分

update thinkpad.customer set info =
 XMLQUERY('  declare default element namespace "http://posample.org";
 transform
 copy $newinfo := $INFO
 modify 
    do delete $newinfo/customerinfo/phone
 return $newinfo')
where CID = 1002

6.查询XML数据

下面就是如何使用SQL和XQuery表达式来查询XML数据啦。

重要事项

要查询XML文档中的部分则必须使用XQuery，SQL只能返回XML文档的全部。
XQuery区分大小写，SQL不区分大小写，因此，在使用XQuery时，诸如指定表名和SQL模式名（缺省情况下，这两个名称都是大写）之类的名称一定要小心，即使在SQL上下文中，XQuery仍将区分大小写。

1.检索和过滤XML值

select XMLQUERY(
    'declare default element namespace "http://posample.org";
    for $d in $INFO/customerinfo
    return <out>{$d/name}</out>'
) from thinkpad.customer as c
where xmlexists('
declare default element namespace "http://posample.org";
$INFO/customerinfo/addr[city="Toronto"]')

结果：
 1
 ---------------------------------------------------------------
 <out xmlns="http://posample.org"><name>Kathy Smith</name></out>

2.使用db2-fn:sqlquery时附带参数

values xmlquery(
 ' declare default element namespace "http://posample.org";
 for $d in db2-fn:sqlquery(
  ''select INFO FROM thinkpad.customer where Cid=parameter(1)'',
  $testval)/customerinfo
  return <out>{$d/name}</out>'
  passing 1000 as "testval")

   1
 ---------------------------------------------------------------
 <out xmlns="http://posample.org"><name>Kathy Smith</name></out>

XQuery函数通过使用标识testval将值1000传递至XQuery表达式，然后使用XQuery表达式通过使用PARAMETER标量函数将该值传递至db2-fn:sqlquery函数。

在XQuery上下文中查询

DB2 XQuery特地提供了以下两个内置函数，以与DB2数据库配合使用：db2-fn:sqlquery和db2-fn:xmlcolumn, db2-fn:sqlquery检索作为SQL全查询的结果表的序列。db2-fn:xmlcolumn从xml列中检索序列。
如果查询直接调用XQuery表达式，那么必须在他前面添加不区分大小写的关键字XQUERY。

检索整个XML文档

要检索先前插入到INFO列中的所有XML文档，可以将XQuery与db2-fn:xmlcolumn或db2-fn:sqlquery配合使用。

使用db2-fn:xmlcolumn

要检索INFO列中的所有XML文档，请运行以下查询：

XQUERY db2-fn:xmlcolumn('CUSTOMER.INFO') --这句话效果等同于select info from customer

使用db2-fn:sqlquery

XQUERY db2-fn:sqlquery('select info from THINKPAD.customer') --仍等同于上句

检索部分XML文档

除了检索整个xml文档之外，还可以通过将XQUERY与db2-fn:xmlcolumn或db2-fn:sqlquery配合使用来检索文档片段并过滤文档中存在的值。

使用db2-fn:xmlcolumn

XQUERY declare default element namespace "http://posample.org";
 for $d in db2-fn:xmlcolumn('THINKPAD.CUSTOMER.INFO')/customerinfo
 where $d/addr/city="Toronto"
 return <out>{$d/name}</out> 

结果：<out xmlns="http://posample.org"><name>Kathy Smith</name></out>

db2-fn:xmlcolumn函数从CUSTOMER表的INFO列中检索序列。for 子句将$d 变量绑定至CUSTOMER.INFO列中的每个customerinfo元素。where子句将文档限制为city元素的值为Toronto的元素。

使用db2-fn:sqlquery

XQUERY declare default element namespace "http://posample.org";
 for $d in db2-fn:sqlquery('select INFO from THINKPAD.CUSTOMER where cid<2000')/customerinfo
 where $d/addr/city="Toronto"
 return <out>{$d/name}</out>

效果等同于上面的

使用db2-fn:sqlquery时附带参数

XQUERY declare default element namespace "http://posample.org";
 let $testval := 1000
 for $d in db2-fn:sqlquery('select INFO from THINKPAD.CUSTOMER where cid=parameter(1)',$testval)/customerinfo
 return <out>{$d/name}</out>

7.针对XML模式验证XML文档

8.使用XLST样式表进行变换

这一部分描述如何使用可扩展标记样式表语言变换（XLST）样式表和内置函数XSLTRANSFORM将数据库中的xml文档转换为其他数据格式。
示例XML文档：以一个包含任意数目的大学生记录的XML文档为例，每个student元素包含学生的标识、名字、姓氏、年龄以及就读的大学，以下文档包含两个学生：

<?xml version="1.0" encoding="UTF-8"?>
<students xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <student studentID="1" givenName="Steffen" familyName="Siegmund" age=21 university="Rostock"/>
    <student studentID="2" givenName="Helena" familyName="Schmit" age=23 university="Rostock"/>
</students>

此外，假定您希望抽取xml记录中的信息并创建可在浏览器中查看的HTML WEB页面，要变换信息，需要下列XSLT样式表：

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns="http://www.w3.org/1999/XSL/Transform">
<xsl:param name="headline">
<xsl:param name="showUniversity">
<xsl:template match="students">
    <html>
    <head/>
    <body>
    <h1><xsl:value-of select="$headline"/></h1>
    <table border="1">
    <th>
    <tr>
    <td width="80">studentID</td>
    <td width="200">Given Name</td>
    <td width="200">Family Name</td>
    <td width="50">Age</td>
    <xsl:choose>
        <xsl:when test="$showUniversity=''true''">
            <td width="200">University</td>
        <xsl:when>
    </xsl:choose>
    </tr>
    </th>

    <xsl:apply-templates/>
    </table>
    </body>
    </html>
    </xsl:template>
        <xsl:template match="student">
            <tr>
            <td><xsl:value-of select="@studentID"/></td>
            <td><xsl:value-of select="@givenName"/></td>
            <td><xsl:value-of select="@familyName"/></td>
            <td><xsl:value-of select="@age"/></td>
            <xsl:choose>
                <xsl:when test="$showUniversity = ’’true’’ ">
                    <td><xsl:value-of select="@university"/></td>
                </xsl:when>
            </xsl:choose>
            </tr>
        </xsl:template>
</xsl:stylesheet>

要变换数据：
1.通过运行下列命令来创建两个用于存储XML文档和样式文档的表：

create table xml_data(docid integer,xml_doc xml)
create tacle xml_trans(xmlid integer, xslt_doc clob(im))

2.使用下列insert语句将xml文档和整个xslt样式表插入表中。

INSERT INTO XML_DATA VALUES
    (1,
    ’<?xml version="1.0"?>
    <students xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <student studentID="1" givenName="Steffen" familyName="Siegmund"
    age="21" university="Rostock"/>
    <student studentID="2" givenName="Helena" familyName="Schmidt"
    age="23" university="Rostock"/>
    </students>’
)
INSERT INTO XML_TRANS VALUES
(1,
    ’<?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0"
    ...
    </xsl:stylesheet>’
)

3.通过调用xltransform函数来变换XML文档：

SELECT XSLTRANSFORM (XML_DOC USING XSLT_DOC AS CLOB(1M))
FROM XML_DATA, XML_TRANS WHERE DOCID = 1 and XSLID = 1 ~

输出为：

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h1></h1>
<table border="1">
<th>
<tr>
<td width="80">StudentID</td>
<td width="200">Given Name</td>
<td width="200">Family Name</td>
<td width="50">Age</td>
</tr>
</th>
<tr>
<td>1</td>
<td>Steffen</td><td>Siegmund</td>
<td>21</td>
</tr>
<tr>
<td>2</td><td>Helena</td><td>Schmidt</td>
<td>23</td>
</tr>
</table>
</body>
</html>

解决最开始的两个问题：

1.json->key在xml中是如何实现的？

select XMLCAST( XMLQUERY ('declare default element namespace "http://posample.org";
$INFO/customerinfo/phone') AS VARCHAR(16))
from customer~

结果： 1
 ------------
 416-555-1358
 905-555-7258
 905-555-2937

select XMLCAST( XMLQUERY ('declare default element namespace "http://posample.org";
$INFO/customerinfo/phone/@type') AS VARCHAR(16))
from customer~
结果：
 1
 ----
 work
 work
 work

插入一条：

insert into Customer(cid,info) values(1004,
'<customerinfo xmlns="http://posample.org" Cid="1004">
    <name>Robert Shoemarker</name>
    <phone type="work">905-555-2937</phone>
    <phone type="personal">15098785133</phone>
</customerinfo>')~

json_each类似的那个

insert into Customer(cid,info) values(1005,
'<customerinfo Cid="1005">
<name>Kathy Smith</name>
<addr country="Canada">
<street>25 EastCreek</street>
<city>Markham</city>
<prov-state>Ontario</prov-state>
<pcode-zip>N9C 3T6</pcode-zip>
</addr>
<phone type="work">905-555-7258</phone>
</customerinfo>')~
insert into Customer(cid,info) values(1006,
'<customerinfo Cid="1006">
<name>Robert Shoemaker</name>
<addr country="Canada">
<street>1596 Baseline</street>
<city>Aurora</city>
<prov-state>Ontario</prov-state>
<pcode-zip>N8X 7F8</pcode-zip>
</addr>
<phone type="work">905-555-7258</phone>
<phone type="home">416-555-2937</phone>
<phone type="cell">905-555-8743</phone>
<phone type="cottage">613-555-3278</phone>
</customerinfo>')~

SELECT X.*
FROM XMLTABLE('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
COLUMNS "CUSTNAME" CHAR(30) PATH 'name',
"PHONENUM" XML PATH 'phone')
as X~
 CUSTNAME                           PHONENUM
 ------------------------------     ---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Kathy Smith                    <phone type="work">905-555-7258</phone>
 Robert Shoemaker               <phone type="work">905-555-7258</phone><phone type="home">416-555-2937</phone><phone type="cell">905-555-8743</phone><phone type="cottage">613-555-3278</phone>