Re: [xsl] [ XSLT ] Dynamically Transform xml pages to database queries

Subject: Re: [xsl] [ XSLT ] Dynamically Transform xml pages to database queries
From: Raffaele Sena <raff@xxxxxxxxxxxx>
Date: Sat, 20 May 2006 17:45:11 -0700
One thing that you can do is to match all elements (and attributes if you want) and decide what to do depending on their name.

You may have to scan the document twice, once for the column names and once for the column values. You can do this by passing a "mode" to your templates. Something like:

<xsl:template match="/">
  insert (
    <xsl:apply-templates mode="column-names"/>
  ) values (
    <xsl:apply-templates mode="column-values"/>
  );
</xsl:template>

<xsl:template match="*" mode="column-names">
  <!-- match elements for column names -->
</xsl:template>

<xsl:template match="*' mode="column-values">
</xsl:template>

And you can use the "name()" function to get the name of the current element.

This doesn't seem to me to be the best use of XSLT. Given the nature of the problem I suspect that using a simple SAX parser and writing your logic in the event handler that you provide it may be easier and more efficient.

-- Raffaele



Jyotsna B wrote:
Thanks for the response .
The languagae I am using is JAVA.

Solution suggested works fine for getting values
replaced in query from XML file


But in my project the condition is NOT to hardcode
even the first part of query .Query need to be
generated "dynamically" from XML 'elements' and
'attribute' names .

Because the XML files I receive are not same pattern
They always are different from eachother with element
and attribute strcuture .

The database table name amd column names are NOT known
ahead of time to mention in template <xsl:template >.I
need look at XML file elements and attribute names


Questions :-
How to get this First part of the Query from XSLT with
unknown element & attribute info of XML files to map
to XSLT template definition ?
Please suggest if below 2 points are possible and best
way to achieve this ?
---------
insert into server(not always same tablename "server"
- its diff name for each xml file) (
'oracleAppVersion'not always "oracleAppVersion" -
its diff attribute depending on element for each xml
file)..etc



1)Element Name in XML (0 - Unbounded limit becomes -> Table Name in output query (CANNOT hardcode table name a head of time in XSLT)

2)Attribute Name under above element becomes Column
Name in output query dynamically (CANNOT hardcode
column names a head of time in XSLT)

I can give me more explaination if needed.
Appreciate your time
Thanks
Jyotsna


--- Raffaele Sena <raff@xxxxxxxxxxxx> wrote:



Jyotsna,

you don't mention which language you want to use to
write write your application. I am assuming is Java but the answer
should apply to any language for which you have an XSLT engine.


1) how to read each file from a folder ?
Use the filesystem API. XSLT cannot help you with
this.
Once you have the file name you can read your
document into a DOM or even better use SAX to stream it to the XSLT
processor.


2) how to write a template that generate text:
<xsl:stylesheet...>
<xsl:output method="text"/>
<xsl:template match="/config/server">
insert into server (
'oracleAppVersion',java-version,java-vendor,



java-vm-version,java-vm-vendor,java-vm-name,os-arch,os-name,os-version


 ) values (
  <xsl:value-of select="company/server-version"/>,
  <xsl:value-of select="java/version"/>,
  <xsl:value-of select="java/vendor"/>,
  <xsl:value-of select="java/vm/version"/>,
  <xsl:value-of select="java/vm/vendor"/>,
  <xsl:value-of select="java/vm/name"/>,
  <xsl:value-of select="os/arch"/>,
  <xsl:value-of select="os/name"/>,
  <xsl:value-of select="os/version"/>
 );
</xsl:template>
</xsl:stylesheet>

Note that I left out the namespace (I am lazy) so if
you try this as-is your output will be empty.


I suggest you start with a command line XSLT
processor (or use XMLSpy) and a static xml file. When you get that to work you
can think about writing a program that runs the conversion.


Hope this helps,

-- Raffaele



Jyotsna B wrote:

Hi All,

I am NEWbie to XSLT and in process of learning.Any
code suggestions or direction is greatly

appreciated


I have a list of XML files at certain location

(ex:


D:\xmlfilesloc )
I need to write a program which checks the folder
automatically and if files exists -reads each xml

file


at a time and transforms the xml file (using XSLT

) to


bunch of customized oracle database queries .

Questions :
1)How to read each xml file from a specified

location


dynamically ?
2)How to use XSLT to transform a xml file
dynamically/automatically to bunch of customized
database queries (elements to table name mapping ;
attributes to columns names ;...etc )?


-------------------------------------------
Ex Output I am trying to acheive is queries :- -------------------------------------------
insert into server
('oracleAppVersion',java-version,java-vendor,



java-vm-version,java-vm-vendor,java-vm-name,os-arch,os-name,os-version

)


values('1.0','1.1.9.0','vendorcl','1.2','vendvmor','virtual','a','bb','ccc')

;


I tried using XMLSpy feature "Create DB structure

from


XML Schema" but its not helping to customize the output ..so

I


want to code myself the XSLT transformation to get
desired output queries


-------------------------------
Example of Input XML file :-
-----------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2006 sp1

U


(http://www.altova.com)-->
<config xmlns="http://xxx.y.com/c/";


xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";

xsi:schemaLocation=http://xxx.y.com/c/

id="String">


<domain-name>oracle appserver</domain-name>
<timestamp>2001-12-17T09:30:47.0Z</timestamp>
<server is-admin-server="false" name="String">
<company>
<server-version>1.0</server-version>
</company>
<java>
<version>1.1.9.0</version>
<vendor>vendorcl</vendor>
<vm>
<version>1.2</version>
<vendor>vendvmor</vendor>
<name>virtual</name>
</vm>
</java>
<os>
<arch>a</arch>
<name>bb</name>
<version>ccc</version>
</os>
</server>
</config>


--------------------------- configdetail.xsd - XSD Schema that it macthes ------------ <?xml version="1.0" encoding="UTF-8"?> <xs:schema

xmlns:xs="http://www.w3.org/2001/XMLSchema";


xmlns="http://y.com"; xmlns:inv="http://y.com/c";
targetNamespace="http://y.com/c";
elementFormDefault="qualified"
attributeFormDefault="unqualified">
<xs:import namespace="http://y.com/c";
schemaLocation="../../configmain.xsd"/>
<xs:complexType name="configdetailtype">
<xs:complexContent>
<xs:extension base="cfg:ConfigmainType">
<xs:sequence>
<xs:element name="server" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="oracleAppVersion"
type="xs:string"/>
<xs:element name="java">
 <xs:complexType>
  <xs:sequence>
   <xs:element name="version" type="xs:string"/>
   <xs:element name="vendor" type="xs:string"/>
   <xs:element name="vm">
    <xs:complexType>
     <xs:sequence>
      <xs:element name="version"

type="xs:string"/>


<xs:element name="vendor"

type="xs:string"/>


      <xs:element name="name" type="xs:string"/>
     </xs:sequence>
    </xs:complexType>
   </xs:element>
  </xs:sequence>
 </xs:complexType>
</xs:element>
<xs:element name="os">
 <xs:complexType>
  <xs:sequence>
   <xs:element name="arch" type="xs:string"/>
   <xs:element name="name" type="xs:string"/>
   <xs:element name="version" type="xs:string"/>
  </xs:sequence>
 </xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="name" type="xs:string"
use="required"/>
<xs:attribute name="adminServer" type="xs:boolean"
use="required"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:schema>

I can give me more explaination if needed.
Thanks a lot for your response

=== message truncated ===



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com




!DSPAM:446f7fd266371065012162!

Current Thread