Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Import XML with different levels

Wardiam

By Wardiam
in Importing & Exporting

 Share

This topic is 3964 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Wardiam Contributor

Wardiam

Posted
Posted

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 Grand Master

comment

Posted
Posted

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 Contributor

Wardiam

Posted
  • Author
Posted

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 Grand Master

comment

Posted
Posted

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?

comment Grand Master

comment

Posted
Posted

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

This topic is 3964 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept