使用PL/SQL创建XML [英] Creating XML with PL/SQL
本文介绍了使用PL/SQL创建XML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要使用PL/SQL创建XML，并且在解决方案中存在两个问题.
i need to create XML with PL/SQL and have two problems in my solution.
示例代码如下:
select xmlelement(
"tag_one",
XMLAGG(
xmlelement("user",
xmlforest(
'something' "two"
, 'else' "tree"
, NULL "four"
, (
select sys_xmlagg(
xmlelement(
"secondGoup",
xmlforest(
'true' "five"
, 123 "six"
)
)
)
FROM dual
) "group_tag"
)
)
)
) as "XML_QUERY"
FROM dual
我得到的XML看起来像这样
XML i get looks like this
<tag_one>
<user>
<two>something</two>
<tree>else</tree>
<group_tag>
<ROWSET>
<secondGoup>
<five>true</five>
<six>123</six>
</secondGoup>
</ROWSET>
</group_tag>
</user>
</tag_one>
它的问题是:
1.)我不需要ROWSET标记，也不知道如何删除
1.) I don't need ROWSET tag in it and don't know how to remove it
2.)我需要在XML中添加四个空标签
2.) I need empty tag four in my XML
<four></four>
我谨提一提，可以有多个secondGoup标签，但是这段代码可以毫无问题地生成它们
I sholud mention that there can be multiple secondGoup tags, but this code can generate them without problem
请注意，以防万一您有更好的创建XML的方法
Noting just in case you have some better way of creating this XML
<tag_one>
<user>
<two>something</two>
<tree>else</tree>
<four></four>
<group_tag>
<secondGoup>
<five>true</five>
<six>123</six>
</secondGoup>
<secondGoup>
<five>true</five>
<six>456</six>
</secondGoup>
<secondGoup>
<five>false</five>
<six>789</six>
</secondGoup>
</group_tag>
</user>
</tag_one>
查询五个和六个看起来像这样
Query for five and six looks like this
SELECT t1.field1 "five"
, t2.field2 "six"
FROM table1 t1
JOIN table2 t2 ON t1.field4 = t2.field2
WHERE t2.field3 = 3
这将返回X行数(有时返回1，有时返回2、3、20等).作为示例在WHERE中添加了3，在实际查询中有变量值
And this returns X number of rows (sometimes it returns 1, sometimes 2, 3, 20,...). Added 3 in WHERE just as example, there is variable value in real query
推荐答案
您好是要获得此输出:
<tag_one>
<user>
<two>something</two>
<tree>else</tree>
<four></four>
<group_tag>
<secondGoup>
<five>true</five>
<six>123</six>
</secondGoup>
<secondGoup>
<five>true</five>
<six>456</six>
</secondGoup>
<secondGoup>
<five>true</five>
<six>789</six>
</secondGoup>
</group_tag>
</user>
</tag_one>
这是正确的查询:
SELECT XMLELEMENT (
"tag_one",
XMLAGG (
XMLELEMENT (
"user",
XMLFOREST (
'something' "two",
'else' "tree",
'' "four",
XMLFOREST (
XMLFOREST ('true' "five", 123 "six") "secondGoup",
XMLFOREST ('true' "five", 456 "six") "secondGoup",
XMLFOREST ('true' "five", 789 "six") "secondGoup") "group_tag"))))
AS "XML_QUERY"
FROM DUAL
=========== EDIT ================
再次，对于您的动态查询，我们不再可以使用XMLELEMT和相关函数，因为它们将返回clob并且与它们一起使用将是一团糟，我在这里为您介绍了一个很长的pl/sql块(太大而又复杂的方法，但我可以在您的情况下使用):
Hi Again, for you dynamic query we no longer can use XMLELEMT and related functions because they shall return clob and it will be a mess to work with them, I came to a very long pl/sql block for you here(it's way too larger and complicated, but I make it usable for your case):
DECLARE
L_XMLTYPE XMLTYPE;
L_DOMDOC DBMS_XMLDOM.DOMDOCUMENT;
L_ROOT_NODE DBMS_XMLDOM.DOMNODE;
L_SUPP_NUM_ELEMENT DBMS_XMLDOM.DOMELEMENT;
L_SUPP_NAME_ELEMENT DBMS_XMLDOM.DOMELEMENT;
L_SUPP_NUM_NODE DBMS_XMLDOM.DOMNODE;
L_SUPP_NAME_NODE DBMS_XMLDOM.DOMNODE;
L_SUPP_NUM_TNODE DBMS_XMLDOM.DOMNODE;
L_SUPP_NAME_TNODE DBMS_XMLDOM.DOMNODE;
L_SUPP_NUM_TEXT DBMS_XMLDOM.DOMTEXT;
L_SUPP_NAME_TEXT DBMS_XMLDOM.DOMTEXT;
L_C1 DBMS_XMLDOM.DOMELEMENT;
L_C1_NODE DBMS_XMLDOM.DOMNODE;
L_C2 DBMS_XMLDOM.DOMELEMENT;
L_C2_NODE DBMS_XMLDOM.DOMNODE;
L_C2_TEXT DBMS_XMLDOM.DOMTEXT;
L_C2_TEXT_NODE DBMS_XMLDOM.DOMNODE;
L_SUPPLIER_ELEMENT DBMS_XMLDOM.DOMELEMENT;
L_SUPPLIER_NODE DBMS_XMLDOM.DOMNODE;
L_SUP_NODE DBMS_XMLDOM.DOMNODE;
BEGIN
-- Create an empty XML document
L_DOMDOC := DBMS_XMLDOM.NEWDOMDOCUMENT;
-- Create a root node
L_ROOT_NODE := DBMS_XMLDOM.MAKENODE (L_DOMDOC);
-- Create a new Supplier Node and add it to the root node
L_SUP_NODE :=
DBMS_XMLDOM.APPENDCHILD (
L_ROOT_NODE,
DBMS_XMLDOM.MAKENODE (
DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'tag_one')));
L_C1 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'user');
L_C1_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_SUP_NODE, DBMS_XMLDOM.MAKENODE (L_C1));
L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'two');
L_C2_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));
L_C2_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, 'something');
L_C2_TEXT_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_C2_NODE, DBMS_XMLDOM.MAKENODE (L_C2_TEXT));
L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'three');
L_C2_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));
L_C2_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, 'else');
L_C2_TEXT_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_C2_NODE, DBMS_XMLDOM.MAKENODE (L_C2_TEXT));
L_C2 := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'group_tag');
L_C2_NODE :=
DBMS_XMLDOM.APPENDCHILD (L_C1_NODE, DBMS_XMLDOM.MAKENODE (L_C2));
FOR SUP_REC IN (SELECT T1.FIELD1, T2.FIELD2
FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.FIELD4 = T2.FIELD2
WHERE T2.FIELD3 = 3)
LOOP
-- For each record, create a new Supplier element
-- and add this new Supplier element to the Supplier Parent node
L_SUPPLIER_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'secondGoup');
L_SUPPLIER_NODE :=
DBMS_XMLDOM.APPENDCHILD (
L_C2_NODE,
DBMS_XMLDOM.MAKENODE (L_SUPPLIER_ELEMENT)
);
-- Each Supplier node will get a Number node which contains the Supplier Number as text
L_SUPP_NUM_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'five');
L_SUPP_NUM_NODE :=
DBMS_XMLDOM.APPENDCHILD (
L_SUPPLIER_NODE,
DBMS_XMLDOM.MAKENODE (L_SUPP_NUM_ELEMENT)
);
L_SUPP_NUM_TEXT := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, SUP_REC.FIELD1);
L_SUPP_NUM_TNODE :=
DBMS_XMLDOM.APPENDCHILD (
L_SUPP_NUM_NODE,
DBMS_XMLDOM.MAKENODE (L_SUPP_NUM_TEXT)
);
-- Each Supplier node will get a Name node which contains the Supplier Name as text
L_SUPP_NAME_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'six');
L_SUPP_NAME_NODE :=
DBMS_XMLDOM.APPENDCHILD (
L_SUPPLIER_NODE,
DBMS_XMLDOM.MAKENODE (L_SUPP_NAME_ELEMENT)
);
L_SUPP_NAME_TEXT :=
DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, SUP_REC.FIELD2);
L_SUPP_NAME_TNODE :=
DBMS_XMLDOM.APPENDCHILD (
L_SUPP_NAME_NODE,
DBMS_XMLDOM.MAKENODE (L_SUPP_NAME_TEXT)
);
END LOOP;
L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE (L_DOMDOC);
DBMS_XMLDOM.FREEDOCUMENT (L_DOMDOC);
DBMS_OUTPUT.PUT_LINE (L_XMLTYPE.GETCLOBVAL);
END;
输出:
<tag_one>
<user>
<two>something</two>
<three>else</three>
<group_tag>
<secondGoup>
<five>123</five>
<six>014</six>
</secondGoup>
<secondGoup>
<five>456</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>789</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>012</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>345</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>678</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>901</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>234</five>
<six>011</six>
</secondGoup>
<secondGoup>
<five>567</five>
<six>011</six>
</secondGoup>
</group_tag>
</user>
</tag_one>
这篇关于使用PL/SQL创建XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
