How to flatten multiple tables

[Gal Kahana]

Thought i'd share this with you.

Two things:

1. You can create a table ADOR based on am XML string. also - a user view. this is actually documented in the uPlan UG...but is little known (well...there's a lot of things in the guide).

2. You can use the idea in (1) to feed a multi-table using document with a single CSV file. 

(2) is good for workflows where the data is stored in a custom DB that stores multi rows under one record (XML structures, for instance), or where the existing workflow knows already how to deliver multi rows in  a single record and you don't want to mess with converting it to RDBMS methods.

[hope i make sense...if not, hopefully you'll get it while reading]

in this little entry i'll explain how to do both.

sample is in 

http://dl.dropbox.com/u/6175121/XMPie%20Stuff/Table%20usage%20samples/MyDocument.zip

it probably explains better than anything i do here, so just take a peek at it first, maybe.

How to represent tables in a string

Most of you know that table ADORs and user views can be created by using a SQL query.

What is also possible is to create a table ADOR or user view based on a string. This allows you to fetch data from non SQL-supporting-protocols and use it as a table.

This string can come from anywhere - it can be a field in your DB. it can be a variable value, it can be some constant string that you put directly as the table ADOR definition...doesn't matter.

The format of the string is fairly simple XML. here is an example:

<T nm='aTable'>

           <FD>

                    <F nm='ID'/>

                    <F nm='Name'/>

                    <F nm='Amount'/>

          </FD>

           <R>

                    <ID>1</ID>

                    <Name>Apple</Name>

                    <Amount>5</Amount>

           </R>

           <R>

                   <ID>2</ID>

                   <Name>MS</Name>

                   <Amount>3</Amount>

           </R>

</T>

This string (meaning this XML as a string value) defines a table named aTable (see the nm attribute of the root T tag),

with fields - ID, Name and Amount - defined under the FD tag, and 2 rows. Each Row is represented by an "R" tag and in it, ordered in the same way as in the FD tag, the values for each row.

That's it. Quite obviously, you can have a different string for every recipient. probably want to stick to the same FD but just have different count of Rs and different values.

Now say i had this string as a value of a plan Variable named "A", this is how i would form the expression of the table ador/user view to use it:

@{EMBEDDED_XML} : SELECT @{A}

which means - use the value in A as a table. (prior to version 5.0, you have to state "EMBEDED_XML"...minor spelling mistake...but XMPie took care of that at great expense ;))

Once done, voila - i got me a table object that can now be used as a chart or as a table in my print design. nice. 

Now, how to have a single CSV representing multiple tables

In order to get to a state where single CSV is feeding multi table product, what's left to realize is that you can use this string as a field value in your DB.

In the sample DB i have a table that has ID, first name, last name and stocks. stocks is a string representing a table in the XML format.

here is a little snippet of the DB:

<SAMPLE STARTS HERE>

ID,FirstName,LastName,Stocks

1,Jeff,Bloom,"<T nm='aTable'><FD><F nm='ID'/><F nm='Name'/><F nm='Amount'/></FD><R><ID>1</ID><Name>Apple</Name><Amount>5</Amount></R><R><ID>2</ID><Name>MS</Name><Amount>3</Amount></R></T>"

2,Jack,White,"<T nm='aTable'><FD><F nm='ID'/><F nm='Name'/><F nm='Amount'/></FD><R><ID>1</ID><Name>Apple</Name><Amount>10</Amount></R></T>"

<SAMPLE FINISHES HERE>

hope it looks OK. note that i simply placed the string value in Stocks.

that's it. got me a flat CSV that can feed multi table document production.

Hope this helps,

Gal.

[Timothy Perrett]

This is great info Gal - thanks for contributing it to the group.

Tim

> --
> You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/xmpie-users/-/8oqa5VU0JdwJ.
> To post to this group, send email to xmpie...@googlegroups.com.
> To unsubscribe from this group, send email to xmpie-users...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/xmpie-users?hl=en.