This discussion is archived
4 Replies Latest reply: Jan 28, 2013 4:01 AM by ranit B RSS

Reg : XML generation -

ranit B Expert
Currently Being Moderated
Hi Experts,

I've a query which generates a XML but it is not working for a specific requirement.
SELECT XMLSERIALIZE (
          DOCUMENT XMLELEMENT (
                      "Feed", XMLAttributes('http://www.abc.com/check/2.1' as "xmlns"),
                      XMLAGG (
                         XMLELEMENT (
                            "Interaction",
                            XMLFOREST (
                               "Email" AS "EmailAddress",
                               "Locale" AS "Locale",
                               "First Name" AS "UserName",
                               "User Id" AS "UserID",
                               "Interaction Date" AS "TransactionDate",
                               XMLFOREST (
                                  XMLFOREST (
                                     "Product ID" AS "ExternalId",
                                     "Product" AS "Name",
                                     "Product Image URL" AS "ImageUrl",
                                     "Price" AS "Price") AS "Product") AS "Products")))))
        --INTO v_xml_doc
    FROM xx;
gives
<Interaction>
    <EmailAddress>user@company.com</EmailAddress>
    <Locale>en_US</Locale>
    <UserName>User</UserName>
    <UserID>SomeID</UserID>
    <TransactionDate>2012-01-01T00:00:00.000</TransactionDate>
    <Products>
        <Product>
            <ExternalId>ExternalID-01</ExternalId>
            <Name>Product 01</Name>
            <ImageUrl>http://www.company.com/product-01/image.jpg</ImageUrl>
        </Product>
    </Products>
</Interaction>
<Interaction>
    <EmailAddress>user@company.com</EmailAddress>
    <Locale>en_US</Locale>
    <UserName>User</UserName>
    <UserID>SomeID</UserID>
    <TransactionDate>2012-01-01T00:00:00.000</TransactionDate>
    <Products>
        <Product>
            <ExternalId>ExternalID-02</ExternalId>
            <Name>Product 02</Name>
            <ImageUrl>http://www.company.com/product-02/image.jpg</ImageUrl>
        </Product>
    </Products>
</Interaction>
But, i want if they belong to same User (Id), then generate only 1 <Interaction> tag :
something like this :
<Interaction>
    <EmailAddress>user@company.com</EmailAddress>
    <Locale>en_US</Locale>
    <UserName>User</UserName>
    <UserID>SomeID</UserID>
    <TransactionDate>2012-01-01T00:00:00.000</TransactionDate>
    <Products>
        <Product> --"node 1"
            <ExternalId>ExternalID-01</ExternalId>
            <Name>Product 01</Name>
            <ImageUrl>http://www.company.com/product-01/image.jpg</ImageUrl>
        </Product>
        <Product> --"node 2"
            <ExternalId>ExternalID-02</ExternalId>
            <Name>Product 02</Name>
            <ImageUrl>http://www.company.com/product-02/image.jpg</ImageUrl>
        </Product>
    </Products>
</Interaction>
Any idea?
Help is highly appreciated.

Ranit B.
  • 1. Re: Reg : XML generation -
    odie_63 Guru
    Currently Being Moderated
    Group by common columns :

    (not tested)
    SELECT XMLSERIALIZE (
              DOCUMENT XMLELEMENT (
                          "Feed", XMLAttributes('http://www.abc.com/check/2.1' as "xmlns"),
                          XMLAGG (
                             XMLELEMENT (
                                "Interaction",
                                XMLFOREST (
                                   "Email" AS "EmailAddress",
                                   "Locale" AS "Locale",
                                   "First Name" AS "UserName",
                                   "User Id" AS "UserID",
                                   "Interaction Date" AS "TransactionDate"
                                 ),
                                 XMLELEMENT ("Products",
                                   XMLAGG(
                                    XMLELEMENT ("Product",
                                      XMLFOREST(
                                       "Product ID" AS "ExternalId",
                                       "Product" AS "Name",
                                       "Product Image URL" AS "ImageUrl",
                                       "Price" AS "Price"
                                      )
                                    )
                                   )
                                 ) 
                            )
                          )
                       ) )
            --INTO v_xml_doc
    FROM xx
    GROUP BY "User Id", "Email", "Locale", "First Name", "Interaction Date" ;
  • 2. Re: Reg : XML generation -
    ranit B Expert
    Currently Being Moderated
    Thanks a lottt Odie.

    But, I also wrote something similar to it -
    SELECT XMLSERIALIZE (
              DOCUMENT XMLELEMENT (
                          "Feed", XMLAttributes('http://abc.com/check/5.6' as "xmlns"),
                          XMLAGG (
                             XMLELEMENT (
                                "Interaction",
                                XMLFOREST (
                                   "Email" AS "EmailAddress",
                                   "Locale" AS "Locale",
                                   "First Name" AS "UserName",
                                   "User Id" AS "UserID",
                                  -- "Interaction Date" AS "TransactionDate", 
                                   XMLAGG(
                                   XMLFOREST (
                                      XMLFOREST (
                                         "Product ID" AS "ExternalId",
                                         "Product" AS "Name",
                                         "Product Image URL" AS "ImageUrl",
                                         "Price" AS "Price") AS "Product")) as "Products" )))))
            INTO v_xml_doc
        FROM xx
        group by "Locale","Email","User Id", "First Name","Price";
    But, your query seems to be fast.
    Is there any difference in performance ?
  • 3. Re: Reg : XML generation -
    odie_63 Guru
    Currently Being Moderated
    But, your query seems to be fast.
    Is there any difference in performance ?
    I don't know, I can't test any of them.

    Yours includes the "Price" column in the group, are you sure that's what you want?
  • 4. Re: Reg : XML generation -
    ranit B Expert
    Currently Being Moderated
    No, but if you carefully check I also used an XMLELEMENT over the inner XMLAGG.

    Does that make any difference?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points