4 ms·

It feels wrong to store whole XML document in a single field of a relational database. It makes more sense to parse XML and then store it in an appropriate rela

It feels wrong to store whole XML document in a single field of a relational database. It makes more sense to parse XML and then store it in an appropriate relational manner.

mechanical_fish 15y ago
From the article: I recently used this strategy for storing raw XML data that we are receiving in the background from an API. We parse it, normalize some of it out to our own data model, but keep the raw XML around. This is why you might not want to parse the XML: Maybe you don't control the XML schema or its upgrade schedule. If the API you're querying decides to add additional fields, you won't see them until you rewrite your parser. Or if the API doesn't return perfectly consistent field sets (e.g. perhaps you're actually querying many different APIs to populate the same table, like a table storing the raw RSS feed output from a thousand websites) then you'll either drop the inconsistent portions on the floor or enjoy the thrill of maintaining one parser per possible result fieldset, plus a SQL schema that captures the intersection of all possible result fieldsets.
jacques_chester 15y ago
The problem is that for a while there, XML was hot hot hot and so DB vendors added "XML support!" as a tickbox feature. There's a case to be made that, by storing XML documents in a field, you've managed to violate first normal form. And it's generally downhill from there.
- dmitriy_ko 15y ago
  Exactly! So you are going to store data as XML blob, and then use XPath to query it. You get to use XPath, how cool is that! I'm sure there are cases when storing XML documents in DB is useful -- e.g. if you need to store a complex document structure of which you don't know ahead of time. But the example they provided -- storing beer info is exactly the wrong case do it.
  - jacques_chester 15y ago
    > So you are going to store data as XML blob, and then use XPath to query it. You get to use XPath, how cool is that! I think you've missed my point. Storing XML in a field defeats normalisation and the benefits flowing there from. Being able to query it with XPath just doubles the number of query languages that I will need to know and keep track of in the database. If you want to store XML blobs, use the file system and XPath to your heart's content.
    - dmitriy_ko 15y ago
      I did get your point. I was being sarcastic when I said that using XPath is "cool." I guess should've marked it with <sarcasm></sarcasm> to make it clear ;). I was trying to convey exactly what you just said: it doesn't make sense to use XPath to query relational DB.
      - jacques_chester 15y ago
        My mistake then.

It feels wrong to store whole XML document in a single field of a relational database. It makes more sense to parse XML and then store it in an appropriate relational manner.

mechanical_fish 15y ago

From the article: I recently used this strategy for storing raw XML data that we are receiving in the background from an API. We parse it, normalize some of it out to our own data model, but keep the raw XML around. This is why you might not want to parse the XML: Maybe you don't control the XML schema or its upgrade schedule. If the API you're querying decides to add additional fields, you won't see them until you rewrite your parser. Or if the API doesn't return perfectly consistent field sets (e.g. perhaps you're actually querying many different APIs to populate the same table, like a table storing the raw RSS feed output from a thousand websites) then you'll either drop the inconsistent portions on the floor or enjoy the thrill of maintaining one parser per possible result fieldset, plus a SQL schema that captures the intersection of all possible result fieldsets.

jacques_chester 15y ago

The problem is that for a while there, XML was hot hot hot and so DB vendors added "XML support!" as a tickbox feature. There's a case to be made that, by storing XML documents in a field, you've managed to violate first normal form. And it's generally downhill from there.

dmitriy_ko 15y ago
Exactly! So you are going to store data as XML blob, and then use XPath to query it. You get to use XPath, how cool is that! I'm sure there are cases when storing XML documents in DB is useful -- e.g. if you need to store a complex document structure of which you don't know ahead of time. But the example they provided -- storing beer info is exactly the wrong case do it.
- jacques_chester 15y ago
  > So you are going to store data as XML blob, and then use XPath to query it. You get to use XPath, how cool is that! I think you've missed my point. Storing XML in a field defeats normalisation and the benefits flowing there from. Being able to query it with XPath just doubles the number of query languages that I will need to know and keep track of in the database. If you want to store XML blobs, use the file system and XPath to your heart's content.
  - dmitriy_ko 15y ago
    I did get your point. I was being sarcastic when I said that using XPath is "cool." I guess should've marked it with <sarcasm></sarcasm> to make it clear ;). I was trying to convey exactly what you just said: it doesn't make sense to use XPath to query relational DB.
    - jacques_chester 15y ago
      My mistake then.