Import XML with different levels

Wardiam · January 12, 2014

Hi Everybody,

I have this XML file

<?xml version="1.0" encoding="UTF-8"?>
<mascot_search_results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.matrixscience.com/xmlns/schema/mascot_search_results_2" xsi:schemaLocation="http://www.matrixscience.com/xmlns/schema/mascot_search_results_2 http://procyon.cnb.csic.es/mascot/xmlns/schema/mascot_search_results_2/mascot_search_results_2.xsd"  majorVersion="2" minorVersion="2">
<hits>
<hit number="1">
<protein accession="gi11841">
<prot_desc> ribulose-1,5-bisphosphate carboxylase/oxygenase large subunit [Nicotiana tabacum]</prot_desc>
<prot_score>2193</prot_score>
<prot_mass>57025</prot_mass>
<prot_matches>226</prot_matches>
<prot_matches_sig>181</prot_matches_sig>
<prot_sequences>36</prot_sequences>
<prot_sequences_sig>36</prot_sequences_sig>
<peptide query="597" rank="1" isbold="1" isunique="0">
<pep_exp_mz>438.79</pep_exp_mz>
<pep_exp_mr>875.56</pep_exp_mr>
<pep_exp_z>2</pep_exp_z>
<pep_calc_mr>875.57</pep_calc_mr>
<pep_delta>-0.01</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>41.15</pep_score>
<pep_expect>0.0068</pep_expect>
<pep_res_before>K</pep_res_before>
<pep_seq>LGLSAK</pep_seq>
<pep_res_after>N</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="0.822" />
<quant_pep_ratio name="116/115" ratio="0.900" />
<quant_pep_ratio name="117/115" ratio="0.963" />
<quant_pep_intensity name="114" intensity="3617.86209669266" />
<quant_pep_intensity name="115" intensity="4400.3056086259" />
<quant_pep_intensity name="116" intensity="3960.92470874405" />
<quant_pep_intensity name="117" intensity="4236.31827110162" />
</peptide>
<peptide query="1939" rank="1" isbold="1" isunique="1">
<pep_exp_mz>513.30</pep_exp_mz>
<pep_exp_mr>1024.58</pep_exp_mr>
<pep_exp_z>2</pep_exp_z>
<pep_calc_mr>1024.58</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>36.01</pep_score>
<pep_expect>0.023</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>GGLDFTK</pep_seq>
<pep_res_after>D</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="0.903" />
<quant_pep_ratio name="116/115" ratio="0.862" />
<quant_pep_ratio name="117/115" ratio="1.043" />
<quant_pep_intensity name="114" intensity="2464.36161871621" />
<quant_pep_intensity name="115" intensity="2730.40281063208" />
<quant_pep_intensity name="116" intensity="2353.13801422539" />
<quant_pep_intensity name="117" intensity="2846.72985557275" />
</peptide>
<peptide query="2144" rank="1" isbold="1" isunique="1">
<pep_exp_mz>522.26</pep_exp_mz>
<pep_exp_mr>1042.50</pep_exp_mr>
<pep_exp_z>2</pep_exp_z>
<pep_calc_mr>1042.51</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>30.60</pep_score>
<pep_expect>0.023</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>AVYECLR</pep_seq>
<pep_res_after>G</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="0.769" />
<quant_pep_ratio name="116/115" ratio="0.838" />
<quant_pep_ratio name="117/115" ratio="1.009" />
<quant_pep_intensity name="114" intensity="4636.55267899568" />
<quant_pep_intensity name="115" intensity="6032.12806065071" />
<quant_pep_intensity name="116" intensity="5056.66650398759" />
<quant_pep_intensity name="117" intensity="6089.19683902587" />
</peptide>
<quant_prot_ratio name="114/115" ratio="0.791" n="66" sd="1.033" significant="*" />
<quant_prot_ratio name="116/115" ratio="0.861" n="67" sd="1.036" significant="*" />
<quant_prot_ratio name="117/115" ratio="0.973" n="67" sd="1.030" significant="*" />
</protein>
</hit>
<hit number="2">
<protein accession="NbS00047700g0013.1">
<prot_desc/>
<prot_score>1815</prot_score>
<prot_mass>88724</prot_mass>
<prot_matches>69</prot_matches>
<prot_matches_sig>65</prot_matches_sig>
<prot_sequences>25</prot_sequences>
<prot_sequences_sig>25</prot_sequences_sig>
<peptide query="2840" rank="1" isbold="0" isunique="0">
<pep_exp_mz>366.58</pep_exp_mz>
<pep_exp_mr>1096.72</pep_exp_mr>
<pep_exp_z>3</pep_exp_z>
<pep_calc_mr>1096.72</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>30.73</pep_score>
<pep_expect>0.0061</pep_expect>
<pep_res_before>K</pep_res_before>
<pep_seq>LVVHITK</pep_seq>
<pep_res_after>N</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="0.878" />
<quant_pep_ratio name="116/115" ratio="1.010" />
<quant_pep_ratio name="117/115" ratio="1.133" />
<quant_pep_intensity name="114" intensity="4419.12617729146" />
<quant_pep_intensity name="115" intensity="5031.58906481152" />
<quant_pep_intensity name="116" intensity="5080.43908961913" />
<quant_pep_intensity name="117" intensity="5701.67603019732" />
</peptide>
<peptide query="5465" rank="1" isbold="0" isunique="0">
<pep_exp_mz>422.24</pep_exp_mz>
<pep_exp_mr>1263.70</pep_exp_mr>
<pep_exp_z>3</pep_exp_z>
<pep_calc_mr>1263.71</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>1</pep_miss>
<pep_score>42.59</pep_score>
<pep_expect>0.0051</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>YREAAEIIR</pep_seq>
<pep_res_after>K</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="0.917" />
<quant_pep_ratio name="116/115" ratio="1.302" />
<quant_pep_ratio name="117/115" ratio="0.989" />
<quant_pep_intensity name="114" intensity="1891.0083988502" />
<quant_pep_intensity name="115" intensity="2062.65987340801" />
<quant_pep_intensity name="116" intensity="2685.17616738527" />
<quant_pep_intensity name="117" intensity="2040.15629068642" />
</peptide>
<peptide query="5466" rank="1" isbold="0" isunique="0">
<pep_exp_mz>422.24</pep_exp_mz>
<pep_exp_mr>1263.70</pep_exp_mr>
<pep_exp_z>3</pep_exp_z>
<pep_calc_mr>1263.71</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>1</pep_miss>
<pep_score>47.60</pep_score>
<pep_expect>0.0016</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>YREAAEIIR</pep_seq>
<pep_res_after>K</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="1.021" />
<quant_pep_ratio name="116/115" ratio="1.300" />
<quant_pep_ratio name="117/115" ratio="0.880" />
<quant_pep_intensity name="114" intensity="3009.73427202821" />
<quant_pep_intensity name="115" intensity="2947.84306428985" />
<quant_pep_intensity name="116" intensity="3832.4632774506" />
<quant_pep_intensity name="117" intensity="2592.63128728402" />
</peptide>
<quant_prot_ratio name="114/115" ratio="0.905" n="59" sd="1.018" significant="*" />
<quant_prot_ratio name="116/115" ratio="1.061" n="59" sd="1.021" significant="*" />
<quant_prot_ratio name="117/115" ratio="0.914" n="59" sd="[1.024]" significant="" />
</protein>
</hit>
<hit number="3">
<protein accession="NbS00029684g0026.1">
<prot_desc/>
<prot_score>1737</prot_score>
<prot_mass>83648</prot_mass>
<prot_matches>65</prot_matches>
<prot_matches_sig>57</prot_matches_sig>
<prot_sequences>30</prot_sequences>
<prot_sequences_sig>29</prot_sequences_sig>
<peptide query="1819" rank="1" isbold="0" isunique="0">
<pep_exp_mz>507.82</pep_exp_mz>
<pep_exp_mr>1013.63</pep_exp_mr>
<pep_exp_z>2</pep_exp_z>
<pep_calc_mr>1013.64</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>25.02</pep_score>
<pep_expect>0.1</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>IAGLEVLR</pep_seq>
<pep_res_after>I</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="1.021" />
<quant_pep_ratio name="116/115" ratio="1.073" />
<quant_pep_ratio name="117/115" ratio="0.796" />
<quant_pep_intensity name="114" intensity="2478.43836518221" />
<quant_pep_intensity name="115" intensity="2426.53793728653" />
<quant_pep_intensity name="116" intensity="2604.8833868462" />
<quant_pep_intensity name="117" intensity="1931.11873339246" />
</peptide>
<peptide query="1820" rank="1" isbold="0" isunique="0">
<pep_exp_mz>507.82</pep_exp_mz>
<pep_exp_mr>1013.63</pep_exp_mr>
<pep_exp_z>2</pep_exp_z>
<pep_calc_mr>1013.64</pep_calc_mr>
<pep_delta>-0.00</pep_delta>
<pep_miss>0</pep_miss>
<pep_score>24.06</pep_score>
<pep_expect>0.13</pep_expect>
<pep_res_before>R</pep_res_before>
<pep_seq>IAGLEVLR</pep_seq>
<pep_res_after>I</pep_res_after>
<pep_var_mod/>
<pep_var_mod_pos/>
<quant_pep_ratio name="114/115" ratio="---" />
<quant_pep_ratio name="116/115" ratio="---" />
<quant_pep_ratio name="117/115" ratio="---" />
<quant_pep_intensity name="114" intensity="" />
<quant_pep_intensity name="115" intensity="" />
<quant_pep_intensity name="116" intensity="" />
<quant_pep_intensity name="117" intensity="" />
</peptide>
</hit>
</hits>
<queries>
<query number="1">
<query_moverz>350.20578</query_moverz>
<query_charge>2+</query_charge>
<StringTitle>Locus:1.1.1.5541.2 File:&quot;01_Rep1_14dic13.wiff&quot;</StringTitle>
<RTINSECONDS>4153</RTINSECONDS>
</query>
<query number="2">
<query_moverz>350.20578</query_moverz>
<query_charge>2+</query_charge>
<StringTitle>Locus:1.1.1.5536.2 File:&quot;01_Rep1_14dic13.wiff&quot;</StringTitle>
<RTINSECONDS>4131</RTINSECONDS>
</query>
<query number="3">
<query_moverz>350.2191</query_moverz>
<query_charge>2+,3+,4+</query_charge>
<StringTitle>Locus:1.1.1.5604.2 File:&quot;01_Rep1_14dic13.wiff&quot;</StringTitle>
<RTINSECONDS>4429</RTINSECONDS>
</query>
</queries>
</mascot_search_results>

I would like to import this fields:

hit number
protein accession
prot_score
prot_mass
peptide query
rank
pep_exp_mz
pep_exp_z
pep_score
pep_seq
ratio 114/115
ratio 116/115
ratio 117/115
RTINSECONDS (at the end)

Could anyone help me to do the XSLT template to import it into FM?

Test_XML.zip

comment · January 13, 2014

You won't be able to do anything with the attached file, because it's not a valid XML document. Here are the parser errors:

line 217:
parser error :
Opening and ending tag mismatch: protein line 165 and hit

</hit>

      ^

line 218:
parser error :
expected '>'

</hits>

     ^

line 239:
parser error :
Opening and ending tag mismatch: hits line 3 and mascot_search_results

</mascot_search_results>

                        ^

line 239:
parser error :
Premature end of data in tag mascot_search_results line 2

</mascot_search_results>

                        ^

(null)

If and when you repost a well-formed XML source, please indicate not only the "fields" but also what constitutes a "record". It looks like the source has at least a parent -< child structure, if not more complex, so you should import it several times into several tables. A separate stylesheet will be required for each table.

Wardiam · January 13, 2014

Hi comment,

I initially uploaded a reduced XML file but I have now uploaded the complete XML. You are right, the original data are stored into several tables but when I exported it from the commercial software, I obtained this file with different levels: protein, peptide and at the end of the document the query info.

Each protein record starts with: <hit number="...">
that contains several peptide records starting with: <peptide query="..."
and at the end of the document the query info that start with: <query number="...">

Then, would I need a separate stylesheet for each table?

Wardiam

comment · January 13, 2014

If you intend to preserve the protein -< peptides hierarchy and import the data into two tables then yes, you need two separate stylesheets. I don't see how the queries are related to the other two, but if want to import them into their own table, then that's another stylesheet.

A stylesheet for import by Filemaker lists the fields that are available for importing; you will see these fields in the import dialog, so that you can map them to the fields in the target table. The second part of the stylesheet tells the processor which data from the source XML is supposed to feed those fields.

I have now uploaded the complete XML.

Where is it?

Wardiam · January 13, 2014

Here, sorry

Test_complete.zip

comment · January 13, 2014

Ouch. That's too big for testing - I can't give up that much CPU time. Anyway, try the two attached stylesheets. Each imports a few fields into its respective table. Expanding this is pretty much a "monkey see, monkey do" matter. Note that the order of FIELDs is matched to the order of COLs.

Compressed file.zip

Wardiam · January 14, 2014

Thanks, I know it's very heavy file. I'm going to test it now.

Wardiam

Sign In

Import XML with different levels

Recommended Posts

Wardiam

Link to comment

Share on other sites

comment

Link to comment

Share on other sites

Wardiam

Link to comment

Share on other sites

comment

Link to comment

Share on other sites

Wardiam

Link to comment

Share on other sites

comment

Link to comment

Share on other sites

Wardiam

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Similar Content

Export Records: 'Specify XML and XSL Options' should have 'XSL style sheet Builder' as another option

Scrutinizer Central - an API for real-time developer intelligence

The PubMed Horcrux: Nine XSL to import one XML. Can the import be recursively scripted or somehow automated?

Navigation after import

zulu on linux

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information