上手DB2的xml(1)
??下载了DB2V10.5的中文文档,重点参考其中的DB2pureXML和DB2XQueryRef两个部分。 pureXML概述??pureXML功能部件语序您将格式良好的XML文档存储在具有XML数据类型的数据库表列中,通过将xml数据存储在xml列中,数据可保持其本机分层结构形式,而不是将其作为文本存储或映射为其他数据模型。 查询和更新的方法
XQuery和XPath数据模型??XQuery表达式对XQuery和XPath数据模型(XDM)的实例进行运算并返回数据模型的实例。 节点层次结构
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,那么不会创建索引条目,也不会返回错误。 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文档
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”的文档。
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数据
重要事项
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列中检索序列。 检索整个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:sqlqueryXQUERY db2-fn:sqlquery('select info from THINKPAD.customer') --仍等同于上句
检索部分XML文档除了检索整个xml文档之外,还可以通过将XQUERY与db2-fn:xmlcolumn或db2-fn:sqlquery配合使用来检索文档片段并过滤文档中存在的值。 使用db2-fn:xmlcolumnXQUERY 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:sqlqueryXQUERY 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 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>
要变换数据: 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>
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |