代码之家 › 专栏 › 技术社区 › Ashutosh

用于xml和函数的sql server到db2转换

db2

Ashutosh · 技术社区 · 7 年前

你能帮我把下面的sql server函数转换成db2格式吗。

 CREATE FUNCTION [dbo].[GetKeyStructureXml]
 (

     @pf_wkstn_oid_sh smallint, 

 )

 RETURNS varchar(max)

 AS
 BEGIN

 DECLARE @RtKeys varchar(Max)

 set @RtKeys = (SELECT  rt.rbase_field_name, 
                        pf_wkstn_oid_sh, 
                        pf_wkstn_oid_lng, 
                        ''N'' status_indc, 
                        rt.field_data_type, 
                        rt.field_size,
                        ''Rate Key            '' DisplayType,
                        '''' Author,
                        0 DateCreated,
                        rb.field_level_indc,
                        rb.field_scope_indc
             FROM rt_tmplt_key rt inner join rbase_field_dict rb  
                 on rb.rbase_field_name=rt.rbase_field_name 
                 where pf_wkstn_oid_sh = @pf_wkstn_oid_sh
                 order by rt_key_sqnc_num asc  
                 FOR XML AUTO, BINARY BASE64,root(''TableKeys''))

 RETURN @RtKeys;

 END;

1 回复 | 直到 7 年前

Mark Barinstein 7 年前

例如,我使用XMLSERIALIZE将XML结果转换为文本。一些第三方工具不能使用db2xml数据类型。

create or replace function test_xml(p_CustomerID int)
returns XML
return
with Customer (CustomerID, CustomerType) as (values
  (1, 'S')
, (2, 'A')
)
, SalesOrderHeader (CustomerID, SalesOrderID, Status) as (values
  (1, 11, '5')
, (1, 12, '5')
, (1, 13, '5')
, (1, 14, '5')
, (2, 21, '6')
, (2, 22, '6')
, (2, 23, '6')
, (2, 24, '6')
)
SELECT 
XMLELEMENT(NAME "Cust", XMLATTRIBUTES(Cust.CustomerID as "CustomerID", Cust.CustomerType as "CustomerType"), OrderHeader.ord)
as col
FROM Customer Cust, 
(
select CustomerID
, XMLAGG(XMLELEMENT(NAME "OrderHeader", XMLATTRIBUTES(CustomerID AS "CustomerID", SalesOrderID AS "SalesOrderID", Status as "Status"))) ord
from SalesOrderHeader
group by CustomerID
) OrderHeader  
WHERE Cust.CustomerID = OrderHeader.CustomerID
and Cust.CustomerID=p_CustomerID;

values xmlserialize(test_xml(1) as clob(1k));

Ashutosh 6 年前

CREATE FUNCTION GetKeyStructureXml(v_pf_wkstn_oid_sh smallint) 

RETURNS xml

LANGUAGE SQL

BEGIN ATOMIC

DECLARE v_RtKeys xml;


SET v_RtKeys=(SELECT XMLELEMENT(
              NAME "TableKeys",
              XMLAGG(XMLELEMENT( NAME "rt", XMLAttributes( rt.rbase_field_name AS "rbase_field_name",
                                        rt.wkstn_oid_sh AS "wkstn_oid_sh", 
                                        rt.wkstn_oid_lng AS "wkstn_oid_lng",
                                        rt_key_sqnc_num AS "rt_key_sqnc_num", 
                                        rt.rt_key_rtrvl_cd AS "rt_key_rtrvl_cd",
                                        pf_wkstn_oid_sh AS "pf_wkstn_oid_sh", 
                                        pf_wkstn_oid_lng AS "pf_wkstn_oid_lng", 
                                        'N' as status_indc, 
                                        rt.field_data_type AS "field_data_type", 
                                        rt.field_size AS "field_size",
                                        'Rate Key            ' as "DisplayType",
                                        '' as "Author",
                                        0 as "DateCreated"),
                    XMLELEMENT( NAME "rb", XMLAttributes( rb.field_level_indc AS "field_level_indc",
                                                          rb.field_scope_indc AS "field_scope_indc")))order by rt_key_sqnc_num asc)OPTION NULL ON NULL) 
                           FROM rt_tmplt_key rt inner join rbase_field_dict rb  
                           on rb.rbase_field_name=rt.rbase_field_name 
                           where pf_wkstn_oid_sh = v_pf_wkstn_oid_sh );

RETURN v_RtKeys;