I'm trying to generate some XML and I'm hoping that DBMS_XMLQUERY will do the trick if I can get the query right.
I have two tables:
Create Table Load_Message(tx_id NUMBER,
tx_type VARCHAR2(3),
tx_date DATE,
product_id NUMBER,
product_name VARCHAR2(30),
product_description VARCHAR2(100),
product_code VARCHAR2(12)
);
Create Table Load_Errors(tx_id NUMBER,
tx_type VARCHAR2(3),
Error_Code NUMBER,
Error_Text VARCHAR2(50),
Error_Column VARCHAR2(30)
);
Insert into Load_Message values(1,'CT',sysdate,1,'Toaster','4 slice industrial toaster','TOAST/I/1');
Insert into Load_Errors values(1,'CT',1,'Invalid Product Code Format','PRODUCT_CODE');
Insert into Load_Errors values(1,'CT',2,'Upper Case Only','PRODUCT_NAME');
I want to create XML like
<LOAD_ERROR_MESSAGE>
<MESSAGE>
<TX_ID>1</TX_ID>
<TX_TYPE>CT</TX_TYPE>
<TX_DATE>2006-09-05</TX_DATE>
<PRODUCT_ID>1</PRODUCT_ID>
<PRODUCT_NAME>Toaster</PRODUCT_NAME>
...
<ERROR>
<ERROR_CODE>1</ERROR_CODE>
<ERROR_TEXT>Invalid Product Code Format<ERROR_TEXT>
<ERROR_COLUMN>PRODUCT_CODE</ERROR_COLUMN>
</ERROR>
<ERROR>
<ERROR_CODE>2</ERROR_CODE>
<ERROR_TEXT>Upper Case Only<ERROR_TEXT>
<ERROR_COLUMN>PRODUCT_NAME</ERROR_COLUMN>
</ERROR>
</MESSAGE>
</LOAD_ERROR_MESSAGE>
Any suggestions? It doesn't have to look exactly like that, the specific thing we're after is the repeating subgroup within the main group.
If it can't be done with a single query to DBMS_XMLQUERY or _XMLGEN, what is the recommended way of doing it?