getting XML from Oracle Tables

Question

When i run

SELECT XMLElement("product", 
                  XMLAttributes(fp.col2 AS "attr2",fp.col4 as "attr4",fp.col5 as "attr5",fp.col6 as "attr6")
                  XMLElement(SELECT (XMLElement("dataset",
                                    XMLAttributes(ds.col3 AS "attr3")
                                    FROM Table2 ds
                                   WHERE fp.col1 = ds.col1 and fp.col2 = ds.col2 and ds.col2='ABC')) )
                  )
FROM Table2 fp
WHERE fp.col1 = 'XYZ'

I get error

ORA-00917: missing comma
00917. 00000 -  "missing comma"
*Cause:    
*Action:
Error at Line: 5 Column: 18

I am not able to understand why

I am expecting output like

<product>
  <dataset></dataset>
</product>

also can you point me to tutorials / examples where xml is generated from joiing multiple tables. I need to have a closer look at syntax.

Most of the examples i searched for had xml generated from single table ( employee)

seems like an error to me ... i am new to XMLAttribute , XMLElement , etc .... do i need to use XMLAgg ? can you give me link to a good tutorial ... not looking for simple ones — Lav
– Lav, Commented Jul 18, 2013 at 7:16

kpater87 · Accepted Answer · 2013-07-18 07:57:10Z

1

---EDIT---

I've modified your query. It should work:

SELECT XMLElement("product"
  , XMLAttributes(fp.col2 AS "attr2",fp.col4 as "attr4",fp.col5 as "attr5",fp.col6 as "attr6")
  , (
    SELECT XMLElement("dataset"
      , XMLAttributes(ds.col3 AS "attr3")
    )
    FROM Table2 ds
    WHERE fp.col1 = ds.col1 and fp.col2 = ds.col2 and ds.col2='ABC'
  )
)
FROM Table2 fp
WHERE fp.col1 = 'XYZ';

In your query there is unnecessary XMLElement clause (second one) and before subquery the comma is missing.

edited Jul 18, 2013 at 7:57

answered Jul 18, 2013 at 7:11

kpater87

1,2701 gold badge14 silver badges34 bronze badges

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

8 Comments

Lav Over a year ago

on adding same, still the issue persists ... not sure if i need to use XMLAgg ... looking for more examples

Lav Over a year ago

Thanks KPater87 it worked now , before i accept your answer ... can you point me to good tutorial ... where i need to get parent child elements from joining different tables .... i need to create more complex queries having 5 children and 3 levels of nesting

kpater87 Over a year ago

I don't know good tutorial. But you should start by reading the documentation. Additionaly to creating big queries the clause WITH is quite useful. Good luck!

Lav Over a year ago

sorry man , my mistake .... i only get product from above query not child elements dataset ...not sure data is issue or syntax

kpater87 Over a year ago

Syntax is good. Maybe in your database there is nothing to display as dataset (with this conditions). You should know your data!

|

Collectives™ on Stack Overflow

getting XML from Oracle Tables

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related