英文:
ORA-02287: sequence number not allowed here in Oracle Xml creation with XMLAGG
问题
I am trying to create an XML document in Oracle. It should have a sequence value in it at the top level, and some line level items. The query is working without the line items, as an example
WITH j AS (
SELECT
'abc' test
FROM
dual
GROUP BY
1
)
SELECT
XMLSERIALIZE(DOCUMENT XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
)
))
FROM
j;
produces the output
<WhseSnapshot><mdDC>157</mdDC></WhseSnapshot>
Adding the line items to the query as follows
WITH j AS (
SELECT
'abc' test
FROM
dual
GROUP BY
1
)
SELECT
XMLSERIALIZE(DOCUMENT XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
),
XMLAGG(XMLELEMENT(
"line",
XMLFOREST(j.test AS "test")
))
))
FROM
j;
results in the following error
ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.
Error at Line: 84 Column: 25
Has anyone been able to achieve a similar goal? I am hoping to avoid a PL/SQL block or function and stick to regular SQL if possible.
英文:
I am trying to create an XML document in Oracle. It should have a sequence value in it at the top level, and some line level items. The query is working without the line items, as an example
WITH j AS (
SELECT
'abc' test
FROM
dual
GROUP BY
1
)
SELECT
XMLSERIALIZE(DOCUMENT XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
)
))
FROM
j;
produces the output
<WhseSnapshot><mdDC>157</mdDC></WhseSnapshot>
Adding the line items to the query as follows
WITH j AS (
SELECT
'abc' test
FROM
dual
GROUP BY
1
)
SELECT
XMLSERIALIZE(DOCUMENT XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
),
XMLAGG(XMLELEMENT(
"line",
XMLFOREST(j.test AS "test")
))
))
FROM
j;
results in the following error
ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.
Error at Line: 84 Column: 25
Has anyone been able to achieve a similar goal? I am hoping to avoid a PL/SQL block or function and stick to regular SQL if possible.
答案1
得分: 3
你可以在子查询中聚合表格:
WITH j (test) AS (
SELECT 'abc'
FROM dual
)
SELECT XMLSERIALIZE(
DOCUMENT
XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
),
( SELECT XMLAGG(
XMLELEMENT(
"line",
XMLFOREST(
j.test AS "test"
)
)
)
FROM j
)
)
) AS xml
FROM DUAL;
输出结果如下:
XML |
---|
英文:
You can aggregate the table in a sub-query:
WITH j (test) AS (
SELECT 'abc'
FROM dual
)
SELECT XMLSERIALIZE(
DOCUMENT
XMLELEMENT(
"WhseSnapshot",
XMLELEMENT(
"mdDC",
MD_EXTRACT_ID_SEQ.NEXTVAL
),
( SELECT XMLAGG(
XMLELEMENT(
"line",
XMLFOREST(
j.test AS "test"
)
)
)
FROM j
)
)
) AS xml
FROM DUAL;
Which outputs:
XML |
---|
<WhseSnapshot><mdDC>1</mdDC><line><test>abc</test></line></WhseSnapshot> |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论