Subject Re: Firebird SQL extract to XML: Possible?
Author karolbieniaszewski
--- In, "Sarah" <sarah@...> wrote:
> Hi all -
> I am working with a customer on a project where I need to map their Firebird database data to an xml schema (from another organization) so it can be imported into another database.
> I've been trying to figure out how Firebird data can be exported to XML and have been striking out. Ideally I'd be able to write the SQL extract to produce XML that is perfectly formatted for what I need like I've been able to do with Oracle and MSSQL Server; however, if I can get it in any form of XML I'll be happy. I can then transform it using XSLT if necessary.
> So far my attempts have not panned out so I'm turning to you guys to see if you have ideas.
> I have IBExpert to work with, but if there is a better tool, I'll gladly look into it.
> If you need an example of what I want to do here is what I would do in T-SQL for MSSQL server.
> foo as "bar",
> FOR XML PATH ('elementname'), type
> FROM DBTable.ColumnName
> WHERE ID="fooid"
> When I run the select it would produce a string with formatted XML that looked like this:
> <element>
> <bar>foo</bar>
> </element>
> Is there any chance that Firebird does something similar? If not, what suggestions would you have for getting the data to a point where I could pull it into an XML format?
> Thanks in advance for any advice you might have.
> Sarah

XML is text based then write text concatenation

'<element>' ||
'<bar> || || '</bar>' ||
FROM DBTable f
WHERE f.ID="fooid"

of course simplest is write stored proc for this with combination of execute statement

Karol Bieniaszewski