小建议:扩展XMLType以访问XPath标量函数

我还用一个说明用法的匿名块进一步完善了我的代码。

以下为类型声明：

CREATE OR REPLACE TYPE myXmlType AS OBJECT (

xml XMLType,

CONSTRUCTOR FUNCTION myXmlType

RETURN SELF AS RESULT,

CONSTRUCTOR FUNCTION myXmlType(

p_xml_type IN XMLType )

RETURN SELF AS RESULT,

CONSTRUCTOR FUNCTION myXmlType(

p_xml IN VARCHAR2 )

RETURN SELF AS RESULT,

MEMBER FUNCTION extract(

p_function IN VARCHAR2,

p_xpath IN VARCHAR2 )

RETURN VARCHAR2,

MEMBER FUNCTION extract(

p_xpath IN VARCHAR2 )

RETURN XMLType,

MEMBER FUNCTION existsnode(

p_xpath IN VARCHAR2 )

RETURN NUMBER,

MEMBER FUNCTION getStringVal

RETURN VARCHAR2

)

/

以下为类型主体：

CREATE OR REPLACE TYPE BODY myXmlType AS
-- I would like to EXTEND the XML type but
--I am not allowed to as XMLType is not
--defined a as a normal UDT

-- I could create my own XMLType from scratch
--But it seems kind of silly to me to
--do all of that re-inventing for little gain

-- So instead, I will create my own type
--and EXTEND the existing XMLType
--through the backdoor

-- Null constructor populates xml
--with NULL
-- Overloaded
CONSTRUCTOR FUNCTION myXmlType
RETURN SELF AS RESULT
IS
BEGIN
xml := NULL;
RETURN;
END;

-- XMLType constructor populates xml
--with a variable already formatted
--as XMLType
-- Overloaded
CONSTRUCTOR FUNCTION myXmlType(
p_xml_type IN XMLType )
RETURN SELF AS RESULT
IS
BEGIN
xml := p_xml_type;
RETURN;
END;

-- XMLType constructor creates an xml
--with a varchar2 variable that
--contains VALID XML data
-- Overloaded
CONSTRUCTOR FUNCTION myXmlType(
p_xml IN VARCHAR2 )
RETURN SELF AS RESULT
IS
BEGIN
xml := XMLType( p_xml );
RETURN;
END;

-- User defined Extract
-- This one can return scalar values
-- Overloaded
MEMBER FUNCTION extract(
p_function IN VARCHAR2,
p_xpath IN VARCHAR2 )
RETURN VARCHAR2
IS

v_xsl XMLType;
v_data VARCHAR2(32767);
BEGIN

IF xml IS NOT NULL
THEN
v_xsl := XMLType('
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:template match="' || p_xpath || '">
<xsl:value-of select="' || p_function || '" />
</xsl:template>
</xsl:stylesheet>'
);

v_data := xml.transform(v_xsl).getStringVal();

END IF;

RETURN v_data;

END;

-- Pass through to Oracle XMLType Extract
-- This one can return scalar values
-- Overloaded
MEMBER FUNCTION extract(
p_xpath IN VARCHAR2 )
RETURN XMLType
IS
BEGIN
RETURN xml.extract(p_xpath);
END;

-- Pass through to Oracle XMLType ExistsNode
MEMBER FUNCTION existsnode(
p_xpath IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
RETURN xml.existsnode(p_xpath);
END;

-- Pass through to Oracle XMLType getStringVal()
MEMBER FUNCTION getStringVal
RETURN VARCHAR2
IS
BEGIN
RETURN xml.getStringVal();
END;


END;
/ 

在下面的样本代码中，注意我如何调用getStringVal()，它的运行机制和内置应用相似；我用一个local-name() xPath函数调用extract并返回一个varchar2。

DECLARE
myXml myXmlType;
BEGIN
myXml := myXmlType('<x><data>abc</data></x>');

DBMS_OUTPUT.put_line( myXml.getStringVal() );

DBMS_OUTPUT.put_line(
myXml.extract(
'local-name()',
'/child::node()')
);
END;
/

值得注意的是，如果你仅仅将最常用的函数移动到你的类型中，你仍然可以通过在类型中访问xml属性访问XMLType函数。例如，在类型中运行myXml.xml.getStringVal()和运行myXml.getStringVal()一样方便。我的类型中没有appendChildXml，因此如果你想应用这个函数，我会调用myXml.xml.appendChildXml()。

责任编辑：德东

查看本文国际来源