Assistance with Query for Multiple Rows as XML mapping

[Mark_Lindberg]

Greetings:

I am trying to use the Query for Multiple Rows service to populate a schema that is not 'Flat' in the repeating node.

I have used this service successfully when all elements to be populated are at the same level.

I hope someone can tell me how to enter the mappings in the XML Information panel of the service operation.

I have tried 'ATTRIBS/appt_req', and various other formats I could think of for mapping to the proper element name, but I always get XML errors.

ACTIVES is the root, and ACTIVE is the repeating node.

ATTRIBS does not repeat within ACTIVE.

I am including a sample of the schema I am trying to load, and I can get data into the ACTIVES/ACTIVE level, but I have not been able to address to the ACTIVES/ACTIVE/ATTRIBS elements

I will also have to figure out how to deal with the KEYWORDS node at some point.

Thank You

Mark

  <ACTIVES>
    <ACTIVE>
      <template_key>tkey1</template_key>
      <id>INSTLP0001a</id>
      <ver>1.2</ver>
      <ind_status>Inactive</ind_status>
      <last_mod_user>wade01</last_mod_user>
      <last_mod_dt>2010-19-04</last_mod_dt>
      <last_mod_tm>13:20:00-05:00</last_mod_tm>
      <flg_activate>0</flg_activate>
      <flg_attrib_change>string</flg_attrib_change>     
      <ATTRIBS>
        <appt_req>1</appt_req>
        <ref_md_req>1</ref_md_req>
        <site>CARDIO</site>
        <print_appt_card>1</print_appt_card>
        <send_ref_md_lttr>1</send_ref_md_lttr>
        <no_remind_lttr>1</no_remind_lttr>
        <area_code>612</area_code>
        <phone_prefix>619</phone_prefix>
        <dflt_pcc_notes>This is a test for Template 01 v1.2</dflt_pcc_notes>
        <flag_direct_prn></flag_direct_prn>
      </ATTRIBS>
      <KEYWORDS>
        <KEYWORD>
          <keyword>key 1 v1.2</keyword>
        </KEYWORD>
        <KEYWORD>
          <keyword>key 2 v1.2</keyword>
        </KEYWORD>
        <KEYWORD>
          <keyword>key 3 v1.2</keyword>
        </KEYWORD>
      </KEYWORDS>     
    </ACTIVE>   
  </ACTIVES>

[_Nith_]

Hi Mark,

I don't think it is possible to nest the XML mapping.

This is because your table data will be just two-dimentional. Through your table return several keywords for each template, the query result will look something like:

template_key        id                         ver       keyword

-------------------        ---                         ----       ---------------

tkey1                  INSTLP0001a         1.2      key 1 v1.2

tkey1                  INSTLP0001a         1.2      key 2 v1.2

tkey1                  INSTLP0001a         1.2      key 3 v1.2

Look at the above exhibit. all the column except keyword are repeated.

So, you can achieve this in two ways.

1. Change your schema to have a one level data mapping.

2. Split your schema into three so that you will have three xml files.

Hope this may help you!

Nith

[Mark_Lindberg]

Nith:

Thanks for your reply.

Since I do not control the schema, I have developed a process that runs 3 queries, and uses substring functions to concat and merge the results into the structure called out by the schema.

Not what I would have preferred, but the volume will be low, and not time sensitive.

Thanks Again.

Mark

[_Nith_]

Substring will be a time consuming operation. Would you prefer combining xmls without converting them into strings?

Nith

[Jasmin_Charbonn · Accepted solution]

The other alternative could be to use the XSLT service to re-arrange the xml the way you want.

Jasmin

[_Nith_]

Interesting !

Thanks Jasmin.

[Mark_Lindberg]

Jasmin:

I have not worked with that service before.

Does it work with the repeating nodes output by the Query Multiple?

Do you have any examples?

Thank You

Mark

[Mark_Lindberg]

Nith:

I would prefer to avoid string functions, if possible.

Thanks

Mark

[Jasmin_Charbonn]

It's just a standard xlst service.

You give it xml as an input and it gives out the updated xml as an output based on you xslt.

All the logic is in the xslt.

Jasmin

[_Nith_]

I did a sample to combine the XMLs for your case. Since your requirement is small, I did it without using XSLT.

http://groups.google.com/group/livecycle/web/XML%20Merge%20Test_1-0_2010-05-06-1018.lca

Nith

[Mark_Lindberg]

Hi Nith:

I tried a couple of times to access the LCA you posted, but am getting the following error in the preview step:

Error previewing archive: 'C:\WINDOWS\Temp\adobejb_server1\ArchiveStore\4001\XML Merge Test\1.0\schemas\1.xsd (The system cannot find the path specified)'

Do you know what the issue might be?

Thanks for your help

Mark