nested XML structure from Oracle

Question

Using the SQL Adapter i BizTalk, calling a SQL Stored procedure on a MS SQL DB, I can get a nice nested XML structure using JOIN and FOR XML AUTO, like this

<p code="DK003">
 <ii stamp="2013-01-14T10:27:38.790"value="180.702052"price="184.000000">
   <d Dividend="2.50"DividendDate="2012-03-29T00:00:00" />
 </ii>
 <ii stamp="2013-01-14T10:27:38.790"value="181.702052"price="14.000000">
   <d Dividend="2.50"DividendDate="2012-03-29T00:00:00" />
 </ii>
</p>

How can I get the same nice XML structure on Oracle ??

Rob van Laarhoven · Accepted Answer · 2013-02-27 10:25:34Z

3

Using xmlelement and related functions like :

SELECT XMLELEMENT("Emp",
      XMLATTRIBUTES(e.employee_id AS "ID", e.last_name),
      XMLELEMENT("Dept", e.department_id),
      XMLELEMENT("Salary", e.salary)) AS "Emp Element"
   FROM employees e
   WHERE e.employee_id = 206;


Emp Element
---------------------------------------------------------------
<Emp ID="206" LAST_NAME="Gietz">
  <Dept>110</Dept>
  <Salary>8300</Salary>
</Emp>

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions220.htm and http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm as a starting point

answered Feb 27, 2013 at 10:25

Rob van Laarhoven

8,9552 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Toko · Accepted Answer · 2013-02-28 09:21:51Z

0

The BizTalk WFC Adapter do not support XMLTypes. so no longer need to use XML structure on Oracle.

answered Feb 28, 2013 at 9:21

Toko

1841 gold badge2 silver badges16 bronze badges

Collectives™ on Stack Overflow

nested XML structure from Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related