www.webdeveloper.com
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 33

Thread: XML to XML transformation using XSLT

  1. #16
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    Give the whoe thing a try and you'll see why it's not a good ide. You might be able to make it work but you would have so many things to watch out for. And your results would not be constrained to be well-formed.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  2. #17
    Join Date
    Feb 2009
    Posts
    32
    same thing with your idea, If you ask me it's depends on you specialty.

  3. #18
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    Mine will be constrained to be well formed because I'm using the DOM methods with MSXML to build the tree.

    And how are you going to get rid of "-" characters preceeding every opening tag unless you output everything as a string. And if you do that you'll have to build all your own tags.

    And I'm far more comfortable and familiar with XSLT and XPATH than I am with scripting MSXML.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  4. #19
    Join Date
    Feb 2009
    Posts
    32
    simple, I will open that first in a notepad and will do the replace.
    "-" with "".

  5. #20
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    You're way past the point where it will be easier to just do it my way.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  6. #21
    Join Date
    Feb 2009
    Posts
    32
    Ok, I admit lol, you right.

  7. #22
    Join Date
    Oct 2008
    Posts
    16
    Quote Originally Posted by VodkaProDrinker View Post
    lol, but it's litle bit messy isn't it? I mean, with JS or VB script in EXL it's much eases to make a mistake then use a software to transform to XML and then use Xpath..
    do you mean we can transform this code using XPATH?

    PHP Code:
       <Row ss:AutoFitHeight="0">
        <
    Cell ss:StyleID="s67"><Data ss:Type="Number">38</Data></Cell>
        <
    Cell ss:StyleID="s68"><Data ss:Type="Number">2421</Data></Cell>
        <
    Cell ss:StyleID="s68"><Data ss:Type="Number">6</Data></Cell>
        <
    Cell ss:StyleID="s86"><Data ss:Type="String">FREDERICK FRANCIS</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="Number">2008284998</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="String">AS220</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="String">IJAZAH</Data></Cell>
        <
    Cell ss:StyleID="s77"/>
        <
    Cell ss:StyleID="s77"/>
        <
    Cell ss:StyleID="s70"><Data ss:Type="String">AWAM</Data></Cell>
        <
    Cell ss:StyleID="s77"/>
        <
    Cell ss:StyleID="s77"><Data ss:Type="Number">2500</Data></Cell>
        <
    Cell ss:StyleID="s77"/>
        <
    Cell ss:StyleID="s68"><Data ss:Type="Number">2500</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="Number">4</Data></Cell>
        <
    Cell ss:StyleID="s87"><Data ss:Type="String">KUDAT,SABAH</Data></Cell>
        <
    Cell ss:StyleID="s88"/>
        <
    Cell ss:StyleID="s88"><Data ss:Type="DateTime">2008-10-10T00:00:00.000</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="Number">10</Data></Cell>
        <
    Cell ss:StyleID="s77"><Data ss:Type="Number">5</Data></Cell>
        <
    Cell ss:StyleID="s67" ss:Formula="=SUM(RC[-2]:RC[-1])"><Data ss:Type="Number">15</Data></Cell>
        <
    Cell ss:StyleID="s77"/>
        <
    Cell ss:StyleID="s67"><Data ss:Type="String">ON LINE</Data></Cell>
       </
    Row
    Charles - do we need any special software or just the programming itself?
    do you have the full working code for programming in vbscript/jscript? please i want to learn more on the code..

    thanks
    - Nazirul

  8. #23
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    Here you go. One working example:
    Code:
    dom = new ActiveXObject("msxml2.DOMDocument.3.0");
    dom.async = false;
    dom.validateOnParse = false;
    dom.resolveExternals = false;
    
    node = dom.createProcessingInstruction("xml", "version='1.0'");
    dom.appendChild (node);
    
    root = dom.createElement ('root');
    dom.appendChild (root);
    
    conxString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=summary.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"';
    query = "SELECT * FROM [8Ci$A3:H17]";
    
    conn = new ActiveXObject ('ADODB.Connection');
    conn.open (conxString);
    
    rs = new ActiveXObject ('ADODB.Recordset');
    rs.open (query, conn, 3, 1);
    if (!rs.bof) rs.moveFirst();
    while (!rs.eof) {
    	record = dom.createElement ('record');
    	for (i = 0; i < rs.fields.count; i++) {
    		tagName = String (rs.fields(i).name).replace (/\W/g, '');
    		node = dom.createElement (tagName);
    		node.appendChild (dom.createTextNode (rs.fields(i).value));
    		record.appendChild (node);
    	}
    	root.appendChild (record);
    	rs.moveNext();
    }
    
    dom.save ('summary.xml');
    rs.close();
    conn.close();
    I saved it as "foo.js" and ran it two ways, both by double clicking on the "foo.js" icon and by navigating to its directory in the DOS shell and typing "cscript foo.js".

    You can do that in XPATH but it would be way longer and more complicated.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  9. #24
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    You should be able to figure out how to adapt that. However, note the line
    Code:
    query = "SELECT * FROM [8Ci$A3:H17]";
    The first thing that you should note and that should excite you is that you are using SQL on your Excel data. You can use SQL magic to pull a subset of the data should you choose. The table syntax is a little weird, it's [tab name$data range]. If you want to use the entire used range of the sheet then you can omit the data range but you will still need the dollar sign.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  10. #25
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    And just for kicks, here's another way to turn your Excel into XML. The result is a little less pretty but still much better than what you get by saving it as XML from Excel.
    Code:
    conxString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=summary.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"';
    query = "SELECT * FROM [8Ci$A3:H17]";
    conn = new ActiveXObject ('ADODB.Connection');
    conn.open (conxString);
    rs = new ActiveXObject ('ADODB.Recordset');
    rs.open (query, conn, 3, 1);
    rs.save ('summary.xml', 1);
    rs.close();
    conn.close();
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  11. #26
    Join Date
    Oct 2008
    Posts
    16

    Thumbs up

    awesome! ... thank you so much Charles... it will really help...

    i will go through the code ...

  12. #27
    Join Date
    Oct 2008
    Posts
    16
    i have successfully get the outcome but no luck.. the result empty file.. no idea..

    Code:
    <?xml version="1.0"?>
    <root/>
    i will try to figure it out.. any idea? thanks

  13. #28
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    Make sure you've properly changed the parts in bold below:
    Code:
    dom = new ActiveXObject("msxml2.DOMDocument.3.0");
    dom.async = false;
    dom.validateOnParse = false;
    dom.resolveExternals = false;
    
    node = dom.createProcessingInstruction("xml", "version='1.0'");
    dom.appendChild (node);
    
    root = dom.createElement ('root');
    dom.appendChild (root);
    
    conxString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=summary.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"';
    query = "SELECT * FROM [8Ci$A3:H17]";
    
    conn = new ActiveXObject ('ADODB.Connection');
    conn.open (conxString);
    
    rs = new ActiveXObject ('ADODB.Recordset');
    rs.open (query, conn, 3, 1);
    if (!rs.bof) rs.moveFirst();
    while (!rs.eof) {
    	record = dom.createElement ('record');
    	for (i = 0; i < rs.fields.count; i++) {
    		tagName = String (rs.fields(i).name).replace (/\W/g, '');
    		node = dom.createElement (tagName);
    		node.appendChild (dom.createTextNode (rs.fields(i).value));
    		record.appendChild (node);
    	}
    	root.appendChild (record);
    	rs.moveNext();
    }
    
    dom.save ('summary.xml');
    rs.close();
    conn.close();
    Script needs to know where your Excel file is and where the table in question is.
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

  14. #29
    Join Date
    Oct 2008
    Posts
    16
    HTML Code:
    query = "SELECT * FROM [8Ci$A3:H17]";
    i think the error come from this line ...

    HTML Code:
    query = "SELECT * FROM [Sheet1$A1:C3]";
    is it the correct one... thanks

  15. #30
    Join Date
    Nov 2002
    Location
    Baltimore, Maryland
    Posts
    12,270
    Without seeing your Excel file I couldn't say. I gave you the instructions above for modifying that line. If you are talking about Sheet1 and you want everything off the page then you could just use
    Code:
    query = "SELECT * FROM [Sheet1$]";
    “The power of the Web is in its universality. Access by everyone regardless of disability is an essential aspect.”
    —Tim Berners-Lee, W3C Director and inventor of the World Wide Web

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles