4 Replies Latest reply: Jan 28, 2013 6:01 AM by ranit B RSS

    Reg : XML generation -

    ranit B
      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
          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
            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
              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
                No, but if you carefully check I also used an XMLELEMENT over the inner XMLAGG.

                Does that make any difference?