Import XML with different levels

import
xml

January 12, 201411 yr

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

January 13, 201411 yr

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.

January 13, 201411 yr

Author

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

January 13, 201411 yr

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?

January 13, 201411 yr

Author

Here, sorry

Test_complete.zip

January 13, 201411 yr

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

January 14, 201411 yr

Author

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

Wardiam

Create an account or sign in to comment

https://fmforums.com/topic/90725-import-xml-with-different-levels/

Followers

Go to topic listing

Multi Machine Deployment
Multi Machine Deployment

Arjun · February 25, 20169 yr
- architecture
- deployment
- filemaker
- filemakerpro9
- Tagged with:
  
  architecture
  
  deployment
  
  filemaker
  
  filemakerpro9
  
  filemakerserver9
  
  fms9
  
  xml
  
  xslt
- 1 reply
- 3,204 views
Wim Decorte

February 25, 20169 yr
What is best practice for scripted Imports?
What is best practice for scripted Imports?

winklestork · January 23, 20205 yr
- fmp16
- import
- 3 replies
- 2,865 views
winklestork

January 23, 20205 yr
XML import/export
XML import/export

DPaquin · January 13, 20205 yr
- fmp16
- xml
- 2 replies
- 3,985 views
comment

January 14, 20205 yr
Import update add
Import update add

Joost Miltenburg · November 10, 20196 yr
- bug
- fmp18
- import
- 1 reply
- 2,705 views
comment

November 10, 20196 yr
IMPORTING AVI AND MKV FILES IN FILEMAKER
IMPORTING AVI AND MKV FILES IN FILEMAKER

dkey · December 11, 20196 yr
- fmp15
- import
- video
- 0 replies
- 1,707 views
dkey

December 11, 20196 yr

Sign In

Import XML with different levels

Featured Replies

Create an account or sign in to comment

Similar Content

Important Information

Account

Navigation

Search

Configure browser push notifications

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)

Sign In

Create an account or sign in to comment

Similar Content

Multi Machine Deployment

What is best practice for scripted Imports?

XML import/export

Import update add

IMPORTING AVI AND MKV FILES IN FILEMAKER

Important Information

Configure browser push notifications

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)