Returning XML structures with SQL

Question

Using the next SQL code, running with Oracle 10:

SELECT xmlserialize
    (
    document xmlelement
        (
        "Response", xmlforest
            (
            '00' AS "ReturnCode"
            ), xmlagg
            (
            xmlelement
                (
                "Students", xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 1' AS "Name",
                        '1' AS "Id"
                        )
                    ), xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 2' AS "Name",
                        '2' AS "Id"
                        )
                    )                                                                           
                ) 
            )                               
        ) AS CLOB INDENT
    ) FROM dual

... I get this XML structure:

<Response>
  <ReturnCode>00</ReturnCode>
  <Students>
    <Student>
      <Name>Some 1</Name>
      <Id>1</Id>
    </Student>
    <Student>
      <Name>Some 2</Name>
      <Id>2</Id>
    </Student>
  </Students>
</Response>

... but, I want to get this one (removing the 'root' element):

  <ReturnCode>00</ReturnCode>
  <Students>
    <Student>
      <Name>Some 1</Name>
      <Id>1</Id>
    </Student>
    <Student>
      <Name>Some 2</Name>
      <Id>2</Id>
    </Student>
  </Students>

Several attemps like this didnt work. Is mandatory to have a root element?:

SELECT xmlserialize
    (
    document xmlforest
        (
        '00' AS "ReturnCode"
        ), xmlagg
        (
        xmlelement
            (
            "Students", xmlelement
                (
                "Student", xmlforest
                    (
                    'Some 1' AS "Name",
                    '1' AS "Id"
                    )
                ), xmlelement
                (
                "Student", xmlforest
                    (
                    'Some 2' AS "Name",
                    '2' AS "Id"
                    )
                )                                                                           
            ) 
        ) AS CLOB INDENT
    ) FROM dual

Any help will be appreciated. (This is just a simplification of something more complex I need to do in some project).

Arkadiusz Łukasiewicz · Accepted Answer · 2018-02-19 12:12:18Z

3

The question is why you need this? XML should be "Well Formed"
This means:
- XML documents must have a root element
- XML elements must have a closing tag
- XML tags are case sensitive
- XML elements must be properly nested
- XML attribute values must be quoted

Add extract('/*/*') and change document -> content

SELECT xmlserialize
    (
    content xmlelement
        (
        "Response", xmlforest
            (
            '00' AS "ReturnCode"
            ), xmlagg
            (
            xmlelement
                (
                "Students", xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 1' AS "Name",
                        '1' AS "Id"
                        )
                    ), xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 2' AS "Name",
                        '2' AS "Id"
                        )
                    )                                                                           
                ) 
            )                               
        ).extract('/*/*') AS CLOB INDENT
    ) FROM dual

answered Feb 19, 2018 at 12:12

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

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

1 Comment

markusk Over a year ago

You can avoid calling extract by using xmlconcat(...) to generate a sequence of XML elements instead of wrapping them in xmlelement("Response", ...). See my response at stackoverflow.com/a/48866515/108326 for details.

XING · Accepted Answer · 2018-02-19 12:15:25Z

0

The easiest working way i could think is to use replace. See below:

Select replace(Replace(col,'< >',''),'</ >','') 
from 
(
SELECT xmlserialize
    (
    document xmlelement
        (
         " ", xmlforest --Putting a wide space to differentiate from other tags
            (
            '00' AS "ReturnCode"
            ), xmlagg
            (
            xmlelement
                (
                "Students", xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 1' AS "Name",
                        '1' AS "Id"
                        )
                    ), xmlelement
                    (
                    "Student", xmlforest
                        (
                        'Some 2' AS "Name",
                        '2' AS "Id"
                        )
                    )                                                                           
                ) 
            )                               
        )
    )  AS COL
  FROM dual )

Output:

<ReturnCode>00</ReturnCode>
  <Students>
    <Student>
      <Name>Some 1</Name>
      <Id>1</Id>
    </Student>
    <Student>
      <Name>Some 2</Name>
      <Id>2</Id>
    </Student>
  </Students>

DEMO

edited Feb 19, 2018 at 12:15

answered Feb 19, 2018 at 12:08

XING

9,9164 gold badges25 silver badges40 bronze badges

6 Comments

markusk Over a year ago

No need for string manipulation of XML, just use xmlserialize(content ...) instead of xmlserialize(document ...). See the other answers to this question for examples.

XING Over a year ago

@markusk There is no point answering same thing. What you answered is already been answered by someone and that's why you didnot get any votes. However by downvoting my answer you invited a downvote for repeatition of same answer.

markusk Over a year ago

My downvote was triggered by the use of string manipulation to modify XML (replace) instead of XML operations (xmlconcat, extract).

XING Over a year ago

@markusk There was no need for downvoting. I just showed another way. As i hope you understand removing root anyways destroys the xml structure. Object was to just remove that and i achieved by simplest means. Its not necessary i do what you expect.

markusk Over a year ago

I consider string manipulation with replace to be strictly inferior, hence the downvote. Using a magic string like "wide space" (which is impossible to recognize or distinguish from normal whitespace in most user interfaces) seems hacky and harder to maintain.

|

markusk · Accepted Answer · 2018-02-19 12:59:39Z

-1

Removing the root element means you will no longer have a valid XML structure, but rather a sequence of XML structures. You could construct this by using xmlserialize(content ...) instead of xmlserialize(document ....).

SELECT xmlserialize(content xmlforest(
  '00' as "ReturnCode",
  xmlforest(
            xmlforest('Some 1' AS "Name",
                      '1' AS "Id"
                     ) AS "Student",
            xmlforest('Some 2' AS "Name",
                      '2' AS "Id"
                     ) AS "Student"
           ) AS "Students"
 ) AS CLOB INDENT)
FROM DUAL;

You could also use xmlconcat and xmlelement instead of xmlforest, if you prefer:

SELECT xmlserialize(content xmlconcat(
   xmlelement("ReturnCode", '00'),
   xmlelement("Students",
              xmlelement("Student",
                         xmlelement("Name", 'Some 1'),                                                            
                         xmlelement("Id", '1')
                        ),
              xmlelement("Student",
                         xmlelement("Name", 'Some 2'),
                         xmlelement("Id", '2')
                        ) 
             )
 ) AS CLOB INDENT)
FROM DUAL;

edited Feb 19, 2018 at 12:59

answered Feb 19, 2018 at 12:43

markusk

6,72636 silver badges39 bronze badges

2 Comments

XING Over a year ago

Isn't it the same what @Arkadiusz Łukasiewicz explained ..!! Why do you create repetition.

markusk Over a year ago

@XING My answer avoids the use of extract, and also demonstrates how to use xmlconcat.

Collectives™ on Stack Overflow

Returning XML structures with SQL

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related