cast (xmlserialize(kk as clob) as char(100))
其中kk表示XML列,char表示最终想要的数据类型,xmlserialize序列化函数,cast是类型转化函数
需要说明的是,cast函数并不能把任意数据类型转化为其它任意数据类型
如不能直接把bigint 转化为varchar类型,但可以把bigint类型直接转化为char类型
xmlserialize(kk as char(10))也可以
select count(*) from (
SELECT ID, XMLQUERY('for $w in $ee/外贸销售/销售单/商品/价格 return $w ' PASSING FXML AS "ee") as kk
FROM TTXML ) group by xmlserialize(kk as char(100))
SELECT ID, XMLQUERY('for $w in $ee/外贸销售/销售单/商品/价格 return $w ' PASSING FXML AS "ee") as kk
FROM TTXML ) group by xmlserialize(kk as char(100))
把XML元素在SQL语句中转换成关系表中的列.
select t.Comment#, i.itemname, t.CustomerID, Message from items i,
xmltable('$c/Comments/Comment' passing i.comments as "c"
columns Comment# integer path 'CommentID',
CustomerID integer path 'CustomerID',
Message varchar(100) path 'Message') as t
xmltable('$c/Comments/Comment' passing i.comments as "c"
columns Comment# integer path 'CommentID',
CustomerID integer path 'CustomerID',
Message varchar(100) path 'Message') as t
XML元素和关系表中的元素相互嵌入的例子(SQL嵌XML,XML又嵌入关系表元素)
select clients.name, clients.status from items, clients
where xmlexists('$c/Comments/Comment[CustomerID=$p]'
passing items.comments as "c", clients.id as "p")
where xmlexists('$c/Comments/Comment[CustomerID=$p]'
passing items.comments as "c", clients.id as "p")
用 SQL 查询 DB2 XML 数据 请参见