Back to forum list… Back to How Do I?...

Convert SQL key/value data to XML element/value using SQL key as XML element name (5 replies and 2 comments)

John G
4 years ago
John G 4 years ago

How do I convert SQL key/value data to XML element/value output, where the values in the SQL key column are used as XML element names, with the value of those elements being the data from the SQL value column?

Some images might help explain what I need.

Attached SQL.png shows the data in the database.

More images coming in replies to this post.

 

https://www.realisable.co.uk/wp-content/uploads/2020/05/SQL.png
John G
4 years ago
John G 4 years ago

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

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

John G
4 years ago

Hang on... no image?

John G
4 years ago
John G 4 years ago

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.

John G
4 years ago
John G 4 years ago

IMan.png

John G
4 years ago
John G 4 years ago

XML.png

John G
4 years ago
John G 4 years ago

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.

 

John G
4 years ago

And now I see the Forum won't let me post XML.

Contact

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Request Demo

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Access Downloads

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close