storing a xml file data in DB

Question

I have a xml file and I want to store it contents in DB.How The format of my xml is not fixed 100% I have xml file as shown below

   <Jb>
    <T1>false</T1>
    <T2>
        <email>[email protected]</email>
        <userName>passwd</clusterName>
        <passwd>adobe</clusterURL>
        <url>dummy</url>
        <param1>val </paramY>
            <paramN> val </paramN>

    </t2>

    <fx> 
        <a >351108</a>
        <b >4629  <b>
    </fx >
    </Jb>

I can have N params eg param1,param2 etc (N not fixed)How can i map it to a relatioanl table structure? Any other approach?

cgp · Accepted Answer · 2011-06-17 15:47:23Z

2

This is a one to many relationship. You'll want a table like so:

account
  id
  email
  usernmae

parameters
  account_id
  param_key
  param_value

And then your SQL looks roughly like:

select * from account, parameters where account.id = parameters.account_id

Alternatively, you can use a single column to store all of your key value pairs as well. You can do this as a long string, or a clob (depending on what you think is more appropriate performance-wise and what your needs are in terms of size)

(so there would additional parsing necessary, but if you're not looking to select parameters via SQL, this is the way to go)

edited Jun 17, 2011 at 15:47

answered Jun 17, 2011 at 14:55

cgp

41.4k12 gold badges107 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony the Pony · Accepted Answer · 2011-06-17 14:53:46Z

0

Usually, XML content is stored in a database in a single TEXT or VARCHAR column.

Because of its free-form structure, it is generally not suitable for O/R mapping.

answered Jun 17, 2011 at 14:53

Tony the Pony

41.7k72 gold badges195 silver badges285 bronze badges

Comments

Michael Lowman · Accepted Answer · 2011-06-17 14:56:17Z

0

One option is to insert the XML directly into the table, then use XPath commands to extract the fields on query. XPath is supported by the major databases: MySQL, Oracle, Postgres...

answered Jun 17, 2011 at 14:56

Michael Lowman

3,0881 gold badge22 silver badges37 bronze badges

Comments

GRyan · Accepted Answer · 2011-06-18 07:11:10Z

0

Oracle has XMLDB, their implementation of the XPath functions for searching based on XML. This means that you can define a column as being an XMLType data type, and attach/map schema to this (This aids in validating as well as evolving the XML over time).

XML in MySQL: http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

MySQL appear to support XPath functions as previously mentioned: http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html

But note, these features are still under development in MySQL.

answered Jun 18, 2011 at 7:11

GRyan

261 bronze badge

Collectives™ on Stack Overflow

storing a xml file data in DB

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related