-1

Current project situation.

  1. Get lot of XML from outside system whose size is less than 50KB and if it contains an attachment, the size would be around 5MB MAX. XML structure complexity is medium because of inner nested elements. There are ~70 first level element and then its child and child of child... Storing that XML in String column of MS SQL server.
  2. While storing XML, we read Search criteria field from XML and maintain them in new columns to improve search queries.
  3. Search functionality to display these messages data in the form of list. Search criteria fields(optional ~10 fields) are from XML elements. Parse that XML to show the elements(around 10 -15) in lists
  4. There are chances of reporting functionality too in future.

Challenge with this design: If new functionality introduced to search the list based on new criteria, then need to add one more column in DB table and have to store that field value from XML which is not best part of this design.

Suggested Improvement: Instead of storing an XML in String format, plan is to store it as an XML column to get a rid of extra columns to keep value of search fields and use XML column query for search and fetch.

Question:

  1. Which DB will give me optimum performance in case of search? I have to fetch only the XMLs which are fitting inside that search criteria.
  2. SQL or NoSQL like MongoDB?
  3. Is there any performance metrics available? Or any case study for same?
  4. DB to manage Reporting load.
Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
NGR
  • 1,230
  • 2
  • 15
  • 44

1 Answers1

0

What client language are you using? Java / PHP / C# / ...? Most of them have XML libraries that do what you need. A database is a data repository, not a data manipulator.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I am using Java. But it is not going to help me to search and pick 10 XMLs out of thousands XMLs present in DB based on search criteria? – NGR Jun 08 '17 at 06:47
  • If you need to search on some piece of XML or JSON, it is better to copy it into a separate column and use a real `INDEX` on that column. Otherwise, the code is likely to have to touch _every_ row, parse the XML, and only then filter out what you want -- very inefficient. – Rick James Jun 08 '17 at 19:06