CODEWITHSUNDEEP
javaxmldatabase
akshay
akshay

Reputation: 5285

storing a xml file data in DB

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?

Upvotes: 0

Views: 1158

Answers (4)

GRyan
GRyan

Reputation: 26

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.

Upvotes: 0

cgp
cgp

Reputation: 41381

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)

Upvotes: 2

Michael Lowman
Michael Lowman

Reputation: 3068

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...

Upvotes: 0

Tony the Pony
Tony the Pony

Reputation: 41347

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.

Upvotes: 0

Related Questions