XML staging - tables or files more efficient?

[rhaddur]

Hi,

We are extracting data from a website I.E using hierarchical stage/XML stage and want place it in staging area.

XML file may contain 200 rows

Where should we place this staging data ?
Is it staging files or staging table

Which one is best approach?

[chulett]

To me, there's a lot of "depends" in your answer. What happens next with them?

[ray.wurlod]

As a general rule storing only XML is easier achieved in files than in tables.

Storing XML plus other stuff changes the story.

What - exactly - do you need to achieve?

[Warning: every general rule has exceptions.]

[rhaddur]

Craig and Ray, thank you very much for your response.

The expectation is after we place XML output into staging table or staging file we would like to do some transformation and feed the output to target web based system either in the form of XML file or JSON string. Please tell which one is suitable here.

One more query, in this case should we use staging area? Or should we use Web service/REST API call fetch data and push to target website by calling another web service call?

[kduke]

I agree with Ray. I never store XML in tables. I would prefer to parse the XML and then write to a table. Then I can format it to whatever is need down the line. In a table you can also query it. It is easy to resend something. It is easy to reconcile. How many rows did I process on Tuesday? Lots of benefits. Easy to archive as well. Files are pain to maintain.