Hi, require help with xml formating:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for VMS: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
My xml view:
CREATE OR REPLACE VIEW v_ack_iec_icrp_xml of XMLtype
--XMLSCHEMA "iec62325-451-1-acknowledgement_v8_0.xsd" Element "Acknowledgement_MarketDocument"
with object id (substr(extractvalue(object_value,'/Aknowledgement_MarketDocument/mRID'),1,40))
as
select XMLElement("Acknowledgement_MarketDocument"
, XMLAttributes(
'urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0' as "xmlns"
),
XMLconcat(
XMLelement("mRID",
xmlattributes('ACK_' || EXTRACTVALUE(x.xml_payload,'/Schedule_MarketDocument/mRID') as "v"))
,XMLelement("createdDateTime",
xmlattributes(TO_CHAR(sysdate,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "v"))
,XMLelement("sender_MarketParticipant.mRID",xmlattributes('A01' as "codingScheme",
EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/receiver_MarketParticipant.mRID') as "v"))
,XMLelement("sender_MarketParticipant.marketRole.type",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/receiver_MarketParticipant.marketRole.type') as "v"))
,XMLelement("receiver_MarketParticipant.mRID",xmlattributes('A01' as "codingScheme",
EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/sender_MarketParticipant.mRID') as "v"))
,XMLelement("receiver_MarketParticipant.marketRole.type",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/sender_MarketParticipant.marketRole.type') as "v"))
,XMLelement("received_MarketDocument.mRID",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/mRID') as "v"))
,XMLelement("received_MarketDocument.revisionNumber",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/revisionNumber') as "v"))
,XMLelement("received_MarketDocument.type",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/type') as "v"))
,XMLelement("received_MarketDocument.title",
xmlattributes(x5.value as "v"))
,XMLelement("received_MarketDocument.createdDateTime",
xmlattributes(EXTRACTVALUE(x.xml_payload,
'/Schedule_MarketDocument/createdDateTime') as "v"))
,(SELECT XMLagg(
XMLelement("Reason",
XMLconcat(
XMLelement("code",
xmlattributes(x2.etso_code as "v"))
,XMLelement("text",
xmlattributes(x2.text as "v"))
) /* cat */
) /* ele */
) /*agg */
FROM run_messages x2,
ic_submissions x3,
system_parameters x4,
system_parameters x41
WHERE x2.job_id = x3.job_id
AND x3.job_id = TO_NUMBER(x4.value)
AND x4.parameter = 'SPC_AEL_CURRENT_XML_JOB_ID'
AND x3.doctype = 'ICRP'
AND x2.etso_code IS NOT NULL
AND x41.parameter = 'SPC_IEC_ETSO_CODES'
AND (x2.etso_code = DECODE(x41.value,'ALL',x2.etso_code)
OR x2.etso_code IN ('A01','A02'))
) /*sel 2 */
) /*concat 1 */
).extract('/*') /*top sel-xmlele */
as IcrpIEC_Ack
from iec_schedule_xml x,
job_parameters x5,
system_parameters x6
where x.document_name LIKE x5.value || '%'
and x5.job_id = TO_NUMBER(x6.value)
and x5.parameter = 'PURE_NAME'
AND x6.parameter = 'SPC_AEL_CURRENT_XML_JOB_ID'
/
Result from the view:
<?xml version="1.0" encoding="UTF-8"?>
<Acknowledgement_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0">
<mRID v="ACK_A04_A01_NLL_NGET_20181121"/>
<createdDateTime v="2018-11-20T13:14:22Z"/>
<sender_MarketParticipant.mRID codingScheme="A01" v="10X1001A1001A515"/>
<sender_MarketParticipant.marketRole.type v="A04"/>
<receiver_MarketParticipant.mRID codingScheme="A01" v="TBD"/>
<receiver_MarketParticipant.marketRole.type v="A04"/>
<received_MarketDocument.mRID v="A04_A01_NLL_NGET_20181121"/>
<received_MarketDocument.revisionNumber v="1"/>
<received_MarketDocument.type v="A04"/>
<received_MarketDocument.title v="10V1001C--00022G_48V0000000000132_NLL-ICRP-A04-741_20181121-001"/>
<received_MarketDocument.createdDateTime v="2018-11-20T11:28:00Z"/>
<Reason>
<code v="A01"/>
<text v="Message fully accepted"/>
</Reason>
</Acknowledgement_MarketDocument>
Two questions:
Q1. The first line:
<?xml version="1.0" encoding="UTF-8"?>
How can I make it to show:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
Q2. Throughout the document.
Example:
<createdDateTime v="2018-11-20T13:14:22Z"/>
How can I make it to show:
<createdDateTime>2018-11-20T13:14:22Z</createdDateTime>
Adding the end bit of creationDateTime
Similarly to all tags/attributes.
Thanks in advance
James