Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] How to load 5 level depth XML data file into DB single table ?

Expand Messages
  • Ronald Bourret
    Hello, XML-DBMS does not support this kind of mapping. In particular, it cannot distribute values from parent elements/attributes across multiple rows. For
    Message 1 of 2 , Dec 9, 2009
    • 0 Attachment
      Hello,

      XML-DBMS does not support this kind of mapping. In particular, it cannot
      "distribute" values from parent elements/attributes across multiple
      rows. For example, if you have an XML document of the form:

      <A a1="1">
      <B b1="1" b2="2" />
      <B b1="3" b2="4" />
      <B b1="5" b2="6" />
      <B b1="7" b2="8" />
      </A>

      you cannot use the value of the a1 attribute in rows created from the B
      elements. (If you try to do this, only the data from the A element and
      the last B element will be transferred.) Instead, you need to transform
      this using XSLT into a document with the following structure:

      <rows>
      <row a1="1" b1="1" b2="2" />
      <row a1="1" b1="3" b2="4" />
      <row a1="1" b1="5" b2="6" />
      <row a1="1" b1="7" b2="8" />
      </rows>

      Note that XML-DBMS can use parent attributes with data from descendant
      elements/attributes. For example, XML-DBMS can transfer data directly
      from a document with the following structure:

      <A_elements>
      <A a1="1">
      <B b1="1" b2="2" />
      </A>
      <A a1="1">
      <B b1="3" b2="4" />
      </A>
      <A a1="1">
      <B b1="5" b2="6" />
      </A>
      <A a1="1">
      <B b1="7" b2="8" />
      </A>
      </A_elements>

      The difference is that parents are repeated for each row. This is done
      with the InlineMap element in your mapping document. For more
      information, see:

      http://www.rpbourret.com/xmldbms/faqs/inline.htm

      In your case, this would mean repeating the t_sardisk, date, and time
      elements for each device element. However, since you must transform the
      document anyway, it is probably simpler to transform it to a document
      with multiple rows, as shown above.

      Does this answer your question?

      Thanks,

      -- Ron

      Jean-Pierre wrote:
      > Dear Sir,
      >
      > I'm actually trying to load a 5 level depth XML data file into MySql single table, unfortunately I couldn't design mapping to do this, regarding to mapping language specs (DTD).
      >
      > At first, is it possible to load following file in single table using XML-DBMS without intermediate XSLT processing ?
      >
      > [Table structure]
      >
      > TABLE `t_sardisk`
      > (
      > `id_sardisk` int(10) unsigned NOT NULL AUTO_INCREMENT,
      >
      > `date` date NOT NULL,
      > `time` time NOT NULL,
      > `device` varchar(45) NOT NULL,
      > `id_server` int(10) unsigned NOT NULL,
      >
      > `busy` int(10) unsigned NOT NULL,
      > `avque` int(10) unsigned NOT NULL,
      > `rws` int(10) unsigned NOT NULL,
      > `blkss` int(10) unsigned NOT NULL,
      > `avwait` int(10) unsigned NOT NULL,
      > `avserv` int(10) unsigned NOT NULL,
      >
      > PRIMARY KEY (`id_sardisk`,`date`,`time`,`device`,`id_server`)
      > )
      >
      > [File sample]
      >
      > <t_sardisk id_server="1">
      > <date value="2009-01-01">
      > <time value="00:15:01">
      > <device id="c4t6d0">
      > <busy value="5.68"/>
      > <avque value="0.73"/>
      > <rws value="22"/>
      > <blkss value="293"/>
      > <avwait value="0.45"/>
      > <avserv value="3.28"/>
      > </device>
      > <device id="c8t0d5">
      > <busy value="22.44"/>
      > <avque value="5.51"/>
      > <rws value="156"/>
      > <blkss value="4712"/>
      > <avwait value="1.12"/>
      > <avserv value="3.63"/>
      > </device>
      > <device id="c8t0d7">
      > <busy value="22.21"/>
      > <avque value="5.16"/>
      > <rws value="155"/>
      > <blkss value="4649"/>
      > <avwait value="0.81"/>
      > <avserv value="3.59"/>
      > </device>
      > <device id="c8t0d6">
      > <busy value="22.61"/>
      > <avque value="4.79"/>
      > <rws value="162"/>
      > <blkss value="4943"/>
      > <avwait value="1.19"/>
      > <avserv value="3.58"/>
      > </device>
      > </time>
      > </date>
      > </t_sardisk>
      >
      > Note : the four first top levels are table primary key components.
      >
      > [Awaited mapping]
      >
      > - XML attribute 't_sardisk->id_server' TO DB table field 'id_server' (PK component),
      > - XML attribute 'date->value' TO DB table field 'date' (PK component),
      > - XML attribute 'time->value' TO DB table field 'time' (PK component),
      > - XML attribute 'device->id' TO DB table field 'device' (PK component),
      > - XML fifth level element's attributes 'busy', 'avque', ... TO DB table corresponding fields.
      >
      >
      >
      > Regards,
      >
      > Jean-Pierre L.
    Your message has been successfully submitted and would be delivered to recipients shortly.