XML Feed headers « myDBR Forums

myDBR Forums » General discussion

XML Feed headers

(17 posts) (2 voices)

Started by duane
Latest reply from duane

Tags:

duane, Member

I'm trying to make a number of feeds to confirm to various formats, and from what I;ve found so far, the mydbr XML feed capability is missing the ability to add multiple headers. So when I start with
SELECT 'dbr.export.options', 'xml.declaration', '<?xml version="1.0" encoding="utf-8"?>'; SELECT 'dbr.export.options', 'xml.root_tag', 'rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom"'; SELECT 'dbr.export.options', 'xml.root_tag', 'source';

it creates (notice it skips the rss definition):
<?xml version="1.0" encoding="UTF-8" ?> <source> [followed by my content]</source>

when I need:
<?xml version="1.0" encoding="UTF-8" ?> <rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom"> <source> [followed by my content]</source></rss>

and, in fact, I also occasionally need to add other headers like
<?xml version="1.0" encoding="UTF-8" ?> <publisher>publisher name</publisher> <publisherurl>publisher name</publisherurl> <datepublished>publisher name</datepublished> <source> [followed by my content]</source>
(notice only source is closed at the end, not the other headers which are closed in the head)

You can see one example at: http://www.w3schools.com/xml/xml_rss.asp (also notice this has a 'sub headers' within 'channel' followed by each item (=rows)

Any way I can do this in mydbr?
myDBR Team, Key Master

Duane,
we can easily add possibility to add additional header tags (like <publisher>) and support for multiple nested root tags.

The 'sub headers' take bit more work, but can be mapped to dbr.hdr.

We'll keep you posted.

--
myDBR Team
duane, Member

Thanks! Look forward to hearing more....

myDBR Team, Key Master

Duane,
you can try the latest build. Changes to XML export are:

- xml.root_tag can now be nested
- xml.header as new option to include tags between declatation and root
- dbr.hdr support (for first level only) to handle the sub headers

So code like this, will produce your w3schools example:

select 'dbr.export.options', 'xml.beautify', 1;

select 'dbr.export.options', 'xml.declaration', '<?xml version="1.0" encoding="UTF-8" ?>';

select 'dbr.export.options', 'xml.header', '<publisher>myDBR</publisher>';

select 'dbr.export.options', 'xml.root_tag', 'rss version="2.0"';

select 'dbr.export.options', 'xml.root_tag', 'channel';

select 'dbr.rowclass', 'rowclass';

select 'dbr.hidecolumn', 'rowclass';

select 'dbr.hdr', 'title_header', 'title_link', 'title_desc';

select

  title_header as 'title[title_header]',

  title_link as 'link[title_link]',

  title_desc as 'description[title_desc]',

  title,

  link,

  description,

  'item' as 'rowclass'

from xml_data;

--
myDBR Team

duane, Member

Fantastic - that seems to solve the structuring issue - thanks!!

The next (related) issue is that some XML files need to have their data enclosed within:
<![CDATA[some data]]>
and converts the greater than/less than symbols into > and < which (it seems) isn't valid.

I looked through and tried ways of escaping this to prevent this conversion - did I miss something or is this not possible (yet!)

I'm currently adding it like
CONCAT('<![CDATA[', myfieldcolumn, ']]>') AS myfield
myDBR Team, Key Master

Duane,
the CDATA can be used when you know that your data will contain markup characters, but is rarely used. See http://xml.silmaril.ie/cdata.html#home

myDBR will escape the XML special characters ">", "<", "&", and the quotation marks (single and double) and the result should be valid. What is the problem you are having?

--
myDBR Team
duane, Member

Ah - OK. So because mydbr will auto escape the xml special characters, cdata isn't necessary - right? Great - I'll get on with the feeds and see if any of the destinations (3rd party) are OK with them. Thanks for your help! Brilliant support as usual :-)
duane, Member

Hi. this is working very nicely for most feeds now - very flexible!

One thing I can't figure out how to do is the following example:
<?xml version="1.0"?> <rss version="2.0"> <channel> <title>ECF Jobs (FairSay)</title> <image><url>http://mydomin/myPathToImage/image.png</url><link>http://mydomain/myPathToPage/</link></image> <item>.....

Where the <image> tags sub header has tags (vs attributes on the tag) within it!

You can see here that this is valid for RSS 2.0:
https://validator.w3.org/feed/docs/rss2.html#ltimagegtSubelementOfLtchannelgt
myDBR Team, Key Master

Duane,
you could concatenate the extra tags into the 'image'-tag. Currently the problem would be that the tags inside would be escaped as myDBR would treat it as xml-data.

We could add an option which would allow for you to pass in raw data (similar to 'dbr.html:'). That would add bit more flexibility to the header generation.

--
myDBR Team
duane, Member

I did try and add the extra tags into the image tag and, as you say, it escaped the tags which isn't valid. So a way to pass in raw data would be appreciated!

It would also mean I could use the enclosure tag which is like <enclosure someattributes /> (and not a second closing tag - see enclosure at https://validator.w3.org/feed/docs/rss2.html#ltimagegtSubelementOfLtchannelgt)
myDBR Team, Key Master

Duane,
please run the updater and you will now be able to use 'dbr.html:' syntax (concatenate to the start of the string) to use raw data in the headers defined by dbr.hdr.

--
myDBR Team
duane, Member

Excellent - works perfectly for the '<image><url>...' channel header.

It doesn't work for the enclosure within the item (row):
<item> <enclosure url="http://mydomain/video/myvideo.mp4" length="1069871" type="audio/mpeg"/> ...

As I can find a way to add attributes to the 'enclosure' tag (only to '<item>') nor to terminate it with a '/>' (vs </enclosure>). Could enabling dbr.html at the row level be the solution (doesn't work yet).
myDBR Team, Key Master

We can see if the same can be added to any element. For now it works in the header.

--
myDBR Team
myDBR Team, Key Master

Duane,
if you care to try now if it works for you. Now the support for raw data has been extended to all elements.

--
myDBR Team
duane, Member

Thanks. It works to put 'raw' html/xml in the item 'row'. What I can't do is eliminate the opening and closing tag.

So what I should get is:
<item> <enclosure url="http://mydomain/video/myvideo.mp4" length="1069871" type="audio/mpeg"/> ...

What I actually get is:
<item> <enclosure><enclosure url="http://mydomain/video/myvideo.mp4" length="1069871" type="audio/mpeg"/></enclosure> ...

or if I make the row 'name' blank (... AS '') I get
<item> <row><enclosure url="http://mydomain/video/myvideo.mp4" length="1069871" type="audio/mpeg"/></row> ...

I'm using variants of:
CONCAT('dbr.html:', '<enclosure url="http://mydomain/video/myvideo.mp4" length="1069871" type="audio/mpeg" />') AS '' (or 'AS enclosure' or "AS ' '")

Thanks for all your patience in this. Seems the standard has some quirks (e.g. usually it requires closing tags, sometimes the tag is self-closing but with attributes and sometimes the tag is nested!)
myDBR Team, Key Master

Duane,
Now you are able to do it. If you name the data item to be a non-valid tag name (empty or just a number) and use the 'dbr.html:', you won't get any tag names leaving you the raw data.

Thanks for all your patience in this. Seems the standard has some quirks (e.g. usually it requires closing tags, sometimes the tag is self-closing but with attributes and sometimes the tag is nested!)

Nothing wrong with the standard, it's just that we are creating structured XML data from non-stuctured data (rows+columns). Not always that straightforward.

--
myDBR Team
duane, Member

Thanks! All working fine now. Your support is one of the best I've every experienced!

Reply

You must log in to post.