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

How to load 5 level depth XML data file into DB single table ?

Expand Messages
  • Jean-Pierre
    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
    Message 1 of 2 , Dec 9, 2009
    View Source
    • 0 Attachment
      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.


      [Non-text portions of this message have been removed]
    • 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 2 of 2 , Dec 9, 2009
      View Source
      • 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.