Crystal and XML output 1

[ifthenelsenull]

Crystal XI

Is it possible to use Crystal to display XML data? I want to use FOR XML in a query and have it output to the report or I would prefer to get the output in a fashion where end users can cut/paste or export) into excel XML. This is in an environment that doesn't have access to SSMS at all.

Any tips or advice are appreciated.

Thanks

 

[Madawc]

If you want output in two different formats, you can do this using a parameter and separate sections for the two alternatives. Does this help?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[ifthenelsenull]

I can't get XML to function in crystal. If I use For XML (auto or raw) in SSMS I get the XML and can get the schema. If I try to take that XML field and put it into crystal it executes the right number of times but displays no data and crystal calls the datatype image and the hover over shows it as a "blob"


In the production environment it's vendor controlled so no SSMS, SSIS or SSRS. They can use crystal. I'm trying to figure out how to get the data out without having to beg the vendor on an adhoc basis. The data needs to go into an XML because it will be forwarded on to another party who needs it in that format.

 

[Madawc]

Outside my experience, sorry.. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[NickieP]

I have done something similar to create(not read XML) because the xml example I was trying to replicate had a structure that CR and Excel doesn't like(nested XSD).
There was no table type schema available and just one record had 250 columns,250 rows when analysed in Excel, making it hard to tell what was needed.
So,
I opened the xml as a text file and pasted the header text into a text box in the CR report header, and details into the detail section.
Each element or attribute is on its own row to ease editing but each section in CR is just one textbox

I replaced the variable parts (attributes/elements)from my source Access Query or Excel Table.
I have group headers/footers that are conditionally suppressed and all sorts of other fancy CR options on formula fields and sections.
It helps if you create formula fields with same name as the element/attribute name so

CR text within your text box:

<AddressLine1>{@10:OrganisationDetails-Name}</AddressLine1>
<CityOrSuburb>{@15:Address-CityOrSuburb}</CityOrSuburb>
<Location>{@16:Address-Location}</Location>

Output XML:
<AddressLine1>MAERSK LINE</AddressLine1>
<CityOrSuburb>LIVERPOOL</CityOrSuburb>
<Location>GBLIV</Location>


Even if the variable data is a direct field from your DB table, use a formula with a number in the name so you get things in the correct order and can see reused fields

In effect, my CR is a mail merge.

It is possible to output text as xml if you export as txt(NOT XML) and set rows per page to 0, save extension as filename.xml, even though the file extn will show as .txt in the save as window.
My XML works a treat, and I have loaded a million records this way when our IT programmers couldn't do this for me as there were too many variants in the source data(hence conditionaly supressed footers)
I had never used xml before, but I managed to figure it out OK.
Doesn't help READING in the xml though, I had same issue, only helps creating XML.

 

[ifthenelsenull]

This would work. Just create text tags for the XML and drop in the fields... I gotta be honest this is the best idea I've heard. I'm gonna roll with it. Thanks for posting it.