Oracle PL/SQL XMLデータをFunctionで受け取る

PL/SQLでXMLデータを受け取り、処理する方法。
ASP.NET側で入力フォームの情報をXML化し、そのXMLをPL/SQLで受け取って更新処理をするという寸法。
・OracleへのアクセスはODP.NETを使用。更新対象のテーブルはOracleのデフォルト環境scott/tigerにあるDEPTテーブルとしている。

ASP.NET側 ※msgLabelに処理結果を出力する想定

'XML型のデータを作成し更新を行う
Dim blr As StringBuilder = New StringBuilder()

'Oracle更新処理
Dim conStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
Dim ocon As OracleConnection = New OracleConnection(conStr)
Dim oCommand As OracleCommand = New OracleCommand("XML_UPDATER.XML_UPDATE", ocon)
oCommand.CommandType = CommandType.StoredProcedure

Dim rv As OracleParameter = oCommand.Parameters.Add("rv", OracleDbType.Varchar2)
rv.Direction = ParameterDirection.ReturnValue
rv.Size = 10000 'サイズは適当

Dim p1 As OracleParameter = oCommand.Parameters.Add("p1", OracleDbType.XmlType)
p1.Direction = ParameterDirection.Input

blr.Append("<?xml version=""1.0""?><depts>")
blr.Append("<dept>")
blr.Append("<dept><dname>DNAME_HOGE</DNAME><LOC ADRCODE=""1"">LOC_HOGE</LOC>
</DEPT>")
blr.Append("</DEPT>")
blr.Append("</DEPTS>")
p1.Size = blr.Length
p1.Value = blr.ToString

Try
ocon.Open()
oCommand.ExecuteNonQuery()
If IsDBNull(oCommand.Parameters("rv").Value) Or oCommand.Parameters("rv").Value.ToString = "null" Then
msgLabel.Text = "SUCCESS"
Else
msgLabel.Text = oCommand.Parameters("rv").Value.ToString
End If
Catch ex As Exception
msgLabel.Text = "Oracle:" + oCommand.Parameters("rv").Value.ToString + " ASP:" + ex.Message
Finally
ocon.Close()

End Try

PL/SQL側(パッケージを作成。パッケージヘッドは省略)

CREATE OR REPLACE PACKAGE BODY SCOTT.XML_UPDATER
IS
FUNCTION XML_UPDATE(pvXml XMLTYPE ) RETURN VARCHAR2 IS
xmlDoc dbms_xmldom.DomDocument;
nodeList dbms_xmldom.DomNodeList;
lvList dbms_xmldom.DomNode;
lvRow SCOTT.DEPT%ROWTYPE;
BEGIN
xmlDoc := xmldom.newDomDOcument(pvXml);
nodeList := xmldom.getElementsByTagName(xmlDoc, 'DEPT');

IF NOT xmldom.isNull(nodeList) THEN
FOR i IN 0 .. xmldom.getLength(nodeList) - 1 LOOP
lvList := xmldom.item(nodeList,i);
lvRow.DNAME := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'DNAME');

lvRow.LOC := GET_ELEMENT_ATTR_BY_TAG_NAME(lvList,'LOC','ADRCODE');
lvRow.LOC := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'LOC') || '-' || lvRow.LOC;

lvRow.DEPTNO := GET_ELEMENT_VALUE_BY_TAG_NAME(lvList,'DEPTNO');
IF lvRow.DEPTNO IS NULL OR lvRow.DEPTNO = '' THEN
SELECT MAX(DEPTNO) INTO lvRow.DEPTNO FROM SCOTT.DEPT;
lvRow.DEPTNO := lvRow.DEPTNO + 1;
INSERT INTO SCOTT.DEPT VALUES lvRow;
ELSE
UPDATE SCOTT.DEPT SET ROW = lvRow WHERE DEPTNO = lvRow.DEPTNO;
END IF;
END LOOP;
END IF;

COMMIT;
RETURN NULL;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN SQLERRM;
END ;

--テキストノードの値を取得
FUNCTION GET_ELEMENT_VALUE_BY_TAG_NAME(domEl dbms_xmldom.DomElement,tagName VARCHAR2) RETURN VARCHAR2 IS
tempList dbms_xmldom.DomNodeList;
oneItem dbms_xmldom.DomNode;
BEGIN
tempList := xmldom.getElementsByTagName(domEl,tagName);
oneItem := xmldom.item(tempList,0); --要素内に、タグ名に合致するノードは1つしかないと想定
RETURN xmldom.getNodeValue(xmldom.getFirstChild(oneItem));
END ;

FUNCTION GET_ELEMENT_VALUE_BY_TAG_NAME(domNd dbms_xmldom.DomNode,tagName VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN GET_ELEMENT_VALUE_BY_TAG_NAME(xmldom.makeElement(domNd),tagName);
END ;

--ノードの属性値を取得
FUNCTION GET_ELEMENT_ATTR_BY_TAG_NAME(domEl dbms_xmldom.DomElement,tagName VARCHAR2,attrName VARCHAR2) RETURN VARCHAR2 IS
tempList dbms_xmldom.DomNodeList;
oneItem dbms_xmldom.DomElement;
BEGIN
tempList := xmldom.getElementsByTagName(domEl,tagName);
oneItem := xmldom.makeElement(xmldom.item(tempList,0));
RETURN xmldom.getAttribute(oneItem,attrName);
END;

FUNCTION GET_ELEMENT_ATTR_BY_TAG_NAME(domNd dbms_xmldom.DomNode,tagName VARCHAR2,attrName VARCHAR2) RETURN VARCHAR2 IS

BEGIN
RETURN GET_ELEMENT_ATTR_BY_TAG_NAME(xmldom.makeElement(domNd),tagName,attrName);
END;

END XML_UPDATER;
/