This IMan.png shows the data in a Hierarchy transform.

Note that Address type "BT" has Address2, while Address type "ST" does not.

I would like to output the data as shown in XML.png. I want to avoid writing empty elements.

Is it possible to tell an IMan XML Writer to suppress empty elements?  If so, I can change the structure of the SQL data; then all the other problems listed here would be solved.

How can I pivot the key/value SQL data so that the value of ElementName is used as an IMan field name, with the value of that row's ElementValue being the value of that field, without using SQL Pivot?

The presence of optional fields suggests to me that the two addresses have to be in separate IMan transactions.  Is that right? That's probably the most trivial problem here, but still I haven't figured out how to do that.

Can the XML Writer use variable names for element names? It won't accept /Address/%ElementName in its XPath.

Thanks in advance.

IMan.png

XML.png

I don't know what's up with the Forum, but I can't attach the other two images.

I want the above SQL data to be output to XML like this:

    ST
    Finnegans
    333 S 5th Ave
    Minneapolis


    ST
    EC Internet
    1682 Novato Blvd
    Suite 254
    Novato

Note that the structure is slightly different between the two Address elements, with the second containing Address2.