| How to use oracle LTRIM and RTRIM functionality in XSL? [message #152651] |
Mon, 26 December 2005 01:04  |
RMAMYY
Messages: 7
Registered: December 2005
Location: BANGALORE
|
Junior Member |
|
|
I want to load below Xml file into Dept table in my local database schema, while loading I want to remove white spaces . How to use oracle LTRIM and RTRIM functionality in XSL?
<Data>
<DEPT_LOAD>
<DEPTNO>50</NSP>
<DNAME> INFOSYS </DNAME>
<LOC>BANGALORE</LOC>
</DEPT_LOAD>
</Data>
</File>
Thanks&Regards
Mallikarjun
|
|
|
|
| Re: How to use oracle LTRIM and RTRIM functionality in XSL? [message #153060 is a reply to message #152651] |
Wed, 28 December 2005 16:22  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
What mechanism are you using for INSERTing into the relational table and what version of Oracle are you using?
The reason I ask is that there are plenty of solutions for getting
data out of an XML document and into table(s) where you could come up with a solution which didn't require XSL, such as XPath extracts etc. at 9i and above.
|
|
|
|
| Re: How to use oracle LTRIM and RTRIM functionality in XSL? [message #153615 is a reply to message #153060] |
Tue, 03 January 2006 06:07 |
RMAMYY
Messages: 7
Registered: December 2005
Location: BANGALORE
|
Junior Member |
|
|
Hi,
We are using Oracle 9i release 2.
This is the mechanism:
Schema Definition (DEPT_LOAD.xsd)
<?xml version="1.0"?>
<xsd:schema xmlns:REP_ITL_LOAD="http://in-blr-srvfdc1/DEPT_LOAD.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="File" type="File_Type"/>
<xsd:complexType name="File_Type">
<xsd:sequence>
<xsd:element name="Header" type="Header_Type"/>
<xsd:element name="Data" type="Data_Type" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Header_Type">
<xsd:sequence>
<xsd:element name="FileType" type="xsd:string"/>
<xsd:element name="FileName" type="xsd:string"/>
<xsd:element name="CreationDate" type="xsd:date"/>
<xsd:element name="CreationTime" type="xsd:time"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Data_Type">
<xsd:sequence>
<xsd:element name="DEPT_LOAD" type="DEPT_LOAD_Type" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="DEPT_LOAD_Type">
<xsd:all>
<xsd:element name="DEPTNO" type="Dept_Type" nillable="false"/>
<xsd:element name="DNAME" type="Dname_Type" nillable="true"/>
<xsd:element name="LOC" type="Loc_Type" nillable="false"/>
</xsd:all>
</xsd:complexType>
<xsd:simpleType name="Dept_Type">
<xsd:restriction base="xsd:integer">
<xsd:totalDigits value="2"/>
<xsd:fractionDigits value="0"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="Dname_Type">
<xsd:restriction base="xsd:string">
<xsd:maxLength value="14"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="Loc_Type">
<xsd:restriction base="xsd:string">
<xsd:maxLength value="13"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
Style Sheet (DEPT_LOAD.xsl)
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fo="http://www.w3.org/1999/XSL/Format">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="File">
<File>
<!-- Treatment of header -->
<!-- xsl:apply-templates select="Header"/ -->
<!-- Treatment of Data -->
<xsl:for-each select="Data">
<Data>
<xsl:apply-templates/>
</Data>
</xsl:for-each>
<!-- Treatment of ROW -->
<xsl:for-each select="DEPT_LOAD">
<DEPT_LOAD>
<xsl:apply-templates/>
</DEPT_LOAD>
</xsl:for-each>
</File>
</xsl:template>
<!-- Treatment of header -->
<xsl:template match="Header">
<xsl:copy-of select="."/>
</xsl:template>
<xsl:template match="Data">
<xsl:copy-of select="."/>
</xsl:template>
<!-- Treatment of fields for DEPT_LOAD -->
<xsl:template match="DEPTNO">
<DEPTNO>
<xsl:value-of select="."/>
</DEPTNO>
</xsl:template>
<xsl:template match="DNAME">
<DNAME>
<xsl:value-of select="."/>
</DNAME>
</xsl:template>
<xsl:template match="LOC">
<LOC>
<xsl:value-of select="."/>
</LOC>
</xsl:template>
</xsl:stylesheet>
XML doc (DEPT_LOAD.xml)
<File>
<Header>
<FileType>DEPT_LOAD</FileType>
<FileName>DEPT_LOAD.xml</FileName>
<CreationDate>2005-11-30</CreationDate>
<CreationTime>11:20:30</CreationTime>
</Header>
<Data>
<DEPT_LOAD>
<DEPTNO>50</DEPTNO>
<DNAME>INFOSYS</DNAME>
<LOC>BANGALORE</LOC>
</DEPT_LOAD>
</Data>
</File>
Question
The below shown 3 files are used to load an external (Supllied) XML file into Dept table in the database,
Now my question is how to use functions like LTRIM, RTRIM, INSTR, SUBSTR, DECODE…… etc
On the below Schema definition.?
We oracle PL/SQL procedures to load and run the above files.
Thanks&Regards
Mallikarjun
|
|
|
|
| Re: How to use oracle LTRIM and RTRIM functionality in XSL? [message #153695 is a reply to message #152651] |
Tue, 03 January 2006 16:44 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
So, is the DEPT_LOAD.xml file created as a result of the XSL being applied, or is it the input file?
I'd be tempted to not use XSL at all (in this situation, I can't currently see the point of it) and use a simple INSERT INTO SELECT (well, a cursor FOR loop at 9i, there are bugs with INSERT INTO SELECTs until 10g), where you have full control over the inserted data via SQL functions TRIM etc. (which you can either put in the INSERT or in the cursor loop around the EXTRACTVALUE, here, i've just used an XML document with both trailing and leading spaces in the DNAME element and used TRIM in the SELECT), i.e. a 9i solution would look something like this :
SQL> CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(14), loc_type VARCHAR2(13) );
Table created.
SQL> DECLARE
2 x XMLTYPE := XMLTYPE('<File>
3 <Header>
4 <FileType>DEPT_LOAD</FileType>
5 <FileName>DEPT_LOAD.xml</FileName>
6 <CreationDate>2005-11-30</CreationDate>
7 <CreationTime>11:20:30</CreationTime>
8 </Header>
9 <Data>
10 <DEPT_LOAD>
11 <DEPTNO>50</DEPTNO>
12 <DNAME> INFOSYS </DNAME>
13 <LOC>BANGALORE</LOC>
14 </DEPT_LOAD>
15 </Data>
16 </File>');
17 BEGIN
18 FOR i IN ( SELECT
19 EXTRACTVALUE(x.t_val, '/DEPT_LOAD/DEPTNO') deptno,
20 TRIM(BOTH ' ' FROM EXTRACTVALUE(x.t_val, '/DEPT_LOAD/DNAME')) dname,
21 EXTRACTVALUE(x.t_val, '/DEPT_LOAD/LOC') loc_type
22 FROM
23 ( SELECT VALUE(t) t_val
24 FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/File/Data/DEPT_LOAD'))) t ) x
25 )
26 LOOP
27 INSERT INTO dept ( deptno, dname, loc_type )
28 VALUES ( i.deptno, i.dname, i.loc_type );
29 END LOOP;
30 END;
31 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC_TYPE
---------- -------------- -------------
50 INFOSYS BANGALORE
Of course, at 10g (as mentioned before), it becomes the slightly more efficient :
DECLARE
x XMLTYPE := XMLTYPE('<File>
<Header>
<FileType>DEPT_LOAD</FileType>
<FileName>DEPT_LOAD.xml</FileName>
<CreationDate>2005-11-30</CreationDate>
<CreationTime>11:20:30</CreationTime>
</Header>
<Data>
<DEPT_LOAD>
<DEPTNO>50</DEPTNO>
<DNAME> INFOSYS </DNAME>
<LOC>BANGALORE</LOC>
</DEPT_LOAD>
</Data>
</File>');
BEGIN
INSERT INTO dept
SELECT
EXTRACTVALUE(x.t_val, '/DEPT_LOAD/DEPTNO'),
TRIM(BOTH ' ' FROM EXTRACTVALUE(x.t_val, '/DEPT_LOAD/DNAME')),
EXTRACTVALUE(x.t_val, '/DEPT_LOAD/LOC')
FROM
( SELECT VALUE(t) t_val
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/File/Data/DEPT_LOAD'))) t ) x;
END;
Rgds
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
