【IT168技术】在企业应用软件设计中，原来往往只需要与一个SQL数据打交道，现在却往往要连接多个数据源，这些数据源可能是SQL数据库、NoSQL数据、XML文件、网站数据等。这么多复杂的数据，有没有办法让它们构成一个数据中心，以相对统一的方式进行管理和数据处理呢?XQuery是合适的前端。
具体做法如下：
①在企业服务器上设定数据库连接池，并指定连接名称。
②选择合适的xquery引擎，并绑定企业服务器。
③扩展xquery的函数用于操作数据库，比如连接关系数据库，扩展以下函数，原型如下：
declare function sql:select($a as xs:string, $b as xs:string) as node() external;
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
④使用xquery查询各单元的数据，合并处理后返回结果。
⑤使用xquery维护各数据单元。
这样做有以下的优点:
①与Sql一样，对外提供统一查询和维护方式。
②可以以一种统一的方式构建数据中心管理工具。
③按数据特性，选择合适高效的存储单元。
以后的模式是否应该这样:
一些厂商提供各种具有一定特性的(比如Sql、Cache、Xml、Json、 File)的高性能数据单元。
一些厂商提供高性能的数据中心管理工具，主要的查询和维护语言是xquery，也可延伸sql。
数据库厂商不需要面面俱到，只需要提供的存储单元足够高效就可以。
④提高SQL数据库的分布性
⑤提高内容的分布性。
⑥更有效规划内容的存储方式。
当然，这样的做法也存在一些缺点：
①xquery的性能还需要提高。
②增加了数据存储规划复杂性。
下面是具体使用的例子：
查询某类别下产品货号，类别以xml的方式保存在dbxml库，货号放在Sql Server中。
declare function sql:select($a as xs:string, $b as xs:string) as xs:string external;
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
let $sTxt := xqilla:parse-xml($str)
let $ID := $sTxt/search/@ID
let $t := doc("dbxml:/product/productcategory.dbxml/data")//Category[@ID=$ID]
let $k := data($t/@ID|$t//Category/@ID)
let $s := concat("'",string-join($k,"','"),"'")
let $sql := <Sql>
select rtrim(ItemNo) as Code,Name,Spec from ItemDetail where CategoryID in ({$s})
order by ItemNo
FOR XML PATH ('Product'),ROOT ('Products')
</Sql>
let $link := "link_erp1"
return sql:select($link,$sql/text())
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
let $sTxt := xqilla:parse-xml($str)
let $ID := $sTxt/search/@ID
let $t := doc("dbxml:/product/productcategory.dbxml/data")//Category[@ID=$ID]
let $k := data($t/@ID|$t//Category/@ID)
let $s := concat("'",string-join($k,"','"),"'")
let $sql := <Sql>
select rtrim(ItemNo) as Code,Name,Spec from ItemDetail where CategoryID in ({$s})
order by ItemNo
FOR XML PATH ('Product'),ROOT ('Products')
</Sql>
let $link := "link_erp1"
return sql:select($link,$sql/text())
使用xquery查询企业资源。
declare namespace fx = "http://local";
declare function sql:select($a as xs:string, $b as xs:string) as node() external;
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
declare function fx:trim( $arg as xs:string? ) as xs:string
{
replace(replace($arg,'\s+$',''),'^\s+','')
} ;
(:let $ResourceType :="Supplier":)
(:let $ResourceType :="Customer":)
(:let $ResourceType :="Goods":)
let $PageSize := 50
let $PageIndex := 1
let $link := "link_erp1"
return
(
if($ResourceType="Supplier") then
(
let $query := <sql>
DECLARE @PageSize int
DECLARE @PageIndex int
set @PageSize = {$PageSize};
set @PageIndex = {$PageIndex};
WITH Temp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SupplierNo ASC) AS RowNumber,*
FROM Supplier1
)
SELECT rtrim(SupplierID) as "@guid",
rtrim(SupplierNo) as SupplierNo,
rtrim(SupplierShort) as ShortName,
rtrim(SupplierName) as Name,
rtrim(SupplierAddress) as Address,
rtrim(CountryNo) as Country,
rtrim(ProvinceNo) as Province
FROM Temp
WHERE (RowNumber BETWEEN @PageSize*(@PageIndex-1) + 1 AND @PageSize * @PageIndex )
FOR XML PATH ('Supplier'),ROOT ('Suppliers')
</sql>
return sql:select($link,$query/text())
)
else if($ResourceType="Customer") then
(
let $query := <sql>
DECLARE @PageSize int
DECLARE @PageIndex int
set @PageSize = {$PageSize};
set @PageIndex = {$PageIndex};
WITH Temp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CustomerNo ASC) AS RowNumber,*
FROM Customer
)
SELECT rtrim(CustomerID) as "@guid",
rtrim(CustomerNo) as CustomerNo,
rtrim(CustomerShort) as ShortName,
rtrim(CustomerName) as Name,
rtrim(CustomerAddress) as Address,
rtrim(Country) as Country
FROM Temp
WHERE (RowNumber BETWEEN @PageSize*(@PageIndex-1) + 1 AND @PageSize * @PageIndex )
FOR XML PATH ('Customer'),ROOT ('Customers')
</sql>
return sql:select($link,$query/text())
)else
()
)
declare function sql:select($a as xs:string, $b as xs:string) as node() external;
declare function sql:update($a as xs:string, $b as xs:string) as xs:boolean external;
declare function fx:trim( $arg as xs:string? ) as xs:string
{
replace(replace($arg,'\s+$',''),'^\s+','')
} ;
(:let $ResourceType :="Supplier":)
(:let $ResourceType :="Customer":)
(:let $ResourceType :="Goods":)
let $PageSize := 50
let $PageIndex := 1
let $link := "link_erp1"
return
(
if($ResourceType="Supplier") then
(
let $query := <sql>
DECLARE @PageSize int
DECLARE @PageIndex int
set @PageSize = {$PageSize};
set @PageIndex = {$PageIndex};
WITH Temp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SupplierNo ASC) AS RowNumber,*
FROM Supplier1
)
SELECT rtrim(SupplierID) as "@guid",
rtrim(SupplierNo) as SupplierNo,
rtrim(SupplierShort) as ShortName,
rtrim(SupplierName) as Name,
rtrim(SupplierAddress) as Address,
rtrim(CountryNo) as Country,
rtrim(ProvinceNo) as Province
FROM Temp
WHERE (RowNumber BETWEEN @PageSize*(@PageIndex-1) + 1 AND @PageSize * @PageIndex )
FOR XML PATH ('Supplier'),ROOT ('Suppliers')
</sql>
return sql:select($link,$query/text())
)
else if($ResourceType="Customer") then
(
let $query := <sql>
DECLARE @PageSize int
DECLARE @PageIndex int
set @PageSize = {$PageSize};
set @PageIndex = {$PageIndex};
WITH Temp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CustomerNo ASC) AS RowNumber,*
FROM Customer
)
SELECT rtrim(CustomerID) as "@guid",
rtrim(CustomerNo) as CustomerNo,
rtrim(CustomerShort) as ShortName,
rtrim(CustomerName) as Name,
rtrim(CustomerAddress) as Address,
rtrim(Country) as Country
FROM Temp
WHERE (RowNumber BETWEEN @PageSize*(@PageIndex-1) + 1 AND @PageSize * @PageIndex )
FOR XML PATH ('Customer'),ROOT ('Customers')
</sql>
return sql:select($link,$query/text())
)else
()
)
