Logo Home    Page list

DNA Test results list #dna #xml #replace

RootsMagic 4 allows one to add a DNA Test fact to a person and enter the results of two types of test. However, there is no report available for the results making it difficult to verify that all data have been accurately transcribed. The results are stored in the EventTable Details column in XML format, making a direct query of this field difficult to read. These two queries, one for each type of test, produce more easily read results by stripping out much of the XML tagging and formatting the results in list format, tab-delimited for the Y-STR query which has multiple columns.

DNA_mtDNA_locationslist.sql
DNA_Y-STR_markerslist.sql

Sample direct query of a Y-STR DNA Test fact:
Person
DNAtest
Surname, James Gordon
<?xml version="1.0" encoding="UTF-8"?>
<dnatest><type>Y-STR</type><laboratory code="DNAConsultants">DNA Consultants</laboratory><description>TestDesc</description><id>Test/Kit ID</id><haplogroup confirmed="true">Haplogroup</haplogroup><markers><marker code="DYS385a"><name>DYS385a</name><value>5</value></marker><marker code="DYS385b"><name>DYS385b</name><value>10.2</value></marker><marker code="DYS388"><name>DYS388</name><value>6</value></marker><marker code="DYS389i"><name>DYS389i</name><value>11</value></marker><marker code="DYS389ii"><name>DYS389ii</name><value>21</value></marker><marker code="DYS390"><name>DYS390</name><value>17</value></marker><marker code="DYS391"><name>DYS391</name><value>4</value></marker><marker code="DYS392"><name>DYS392</name><value>8</value></marker><marker code="DYS393"><name>DYS393</name><value>6</value></marker><marker code="DYS19"><name>DYS19</name><value>12-15</value></marker><marker code="DYS426"><name>DYS426</name><value>6.2</value></marker><marker code="DYS437"><name>DYS437</name><value>14</value></marker><marker code="DYS438"><name>DYS438</name><value>6</value></marker><marker code="DYS439"><name>DYS439</name><value>10</value></marker><marker code="DYS441"><name>DYS441</name><value>8</value></marker><marker code="DYS442"><name>DYS442</name><value>13</value></marker><marker code="DYS444"><name>DYS444</name><value>8</value></marker><marker code="DYS445"><name>DYS445</name><value>7</value></marker><marker code="DYS446"><name>DYS446</name><value>8</value></marker><marker code="DYS447"><name>DYS447</name><value>18</value></marker><marker code="DYS448"><name>DYS448</name><value>11</value></marker><marker code="DYS449"><name>DYS449</name><value>23</value></marker><marker code="DYS452"><name>DYS452</name><value>7</value></marker><marker code="DYS454"><name>DYS454</name><value>9</value></marker><marker code="DYS455"><name>DYS455</name><value>4</value></marker><marker code="DYS456"><name>DYS456</name><value>16</value></marker><marker code="DYS458"><name>DYS458</name><value>10</value></marker><marker code="DYS459a"><name>DYS459a</name><value>5</value></marker><marker code="DYS459b"><name>DYS459b</name><value>2</value></marker><marker code="DYS460"><name>DYS460</name><value>10.1</value></marker><marker code="DYS461"><name>DYS461</name><value>11-12</value></marker><marker code="DYS462"><name>DYS462</name><value>10</value></marker><marker code="DYS463"><name>DYS463</name><value>5</value></marker><marker code="DYS464a"><name>DYS464a</name><value>10.3</value></marker><marker code="DYS464b"><name>DYS464b</name><value>10.1</value></marker><marker code="DYS464c"><name>DYS464c</name><value>9.3</value></marker><marker code="DYS464d"><name>DYS464d</name><value>10.3</value></marker><marker code="GAAT1B07"><name>GGAAT1B07</name><value>14</value></marker><marker code="YCAIIa"><name>YCAIIa</name><value>16.1</value></marker><marker code="YCAIIb"><name>YCAIIb</name><value>16.1</value></marker><marker code="GATAA10"><name>GATA-A10</name><value>14</value></marker><marker code="DYS635"><name>DYS635</name><value>21-22</value></marker><marker code="GATAH4"><name>GATA-H4</name><value>12</value></marker><marker code="DYS465X"><name>DYS464X</name><value>4</value></marker><marker code="DYS464f"><name>DYS464f</name><value>10.3</value></marker><marker code="DYS481"><name>DYS481</name><value>22</value></marker><marker code="DYS485"><name>DYS485</name><value>15</value></marker><marker code="DYS487"><name>DYS487</name><value>12</value></marker></markers></dnatest>

Above processed by DNA_Y-STR_markerslist query:
Person
DNAtest
Surname, James Gordon
"<?xml version="1.0" encoding="UTF-8"?>
<dnatest><type>Y-STR</type><laboratory code="DNAConsultants">DNA Consultants</laboratory><description>TestDesc</description><id>Test/Kit ID</id><haplogroup confirmed="true">Haplogroup</haplogroup>
<marker code="DYS385a"> DYS385a 5
<marker code="DYS385b"> DYS385b 10.2
<marker code="DYS388"> DYS388 6
<marker code="DYS389i"> DYS389i 11
<marker code="DYS389ii"> DYS389ii 21
<marker code="DYS390"> DYS390 17
<marker code="DYS391"> DYS391 4
<marker code="DYS392"> DYS392 8
<marker code="DYS393"> DYS393 6
<marker code="DYS19"> DYS19 12-15
<marker code="DYS426"> DYS426 6.2
<marker code="DYS437"> DYS437 14
<marker code="DYS438"> DYS438 6
<marker code="DYS439"> DYS439 10
<marker code="DYS441"> DYS441 8
<marker code="DYS442"> DYS442 13
<marker code="DYS444"> DYS444 8
<marker code="DYS445"> DYS445 7
<marker code="DYS446"> DYS446 8
<marker code="DYS447"> DYS447 18
<marker code="DYS448"> DYS448 11
<marker code="DYS449"> DYS449 23
<marker code="DYS452"> DYS452 7
<marker code="DYS454"> DYS454 9
<marker code="DYS455"> DYS455 4
<marker code="DYS456"> DYS456 16
<marker code="DYS458"> DYS458 10
<marker code="DYS459a"> DYS459a 5
<marker code="DYS459b"> DYS459b 2
<marker code="DYS460"> DYS460 10.1
<marker code="DYS461"> DYS461 11-12
<marker code="DYS462"> DYS462 10
<marker code="DYS463"> DYS463 5
<marker code="DYS464a"> DYS464a 10.3
<marker code="DYS464b"> DYS464b 10.1
<marker code="DYS464c"> DYS464c 9.3
<marker code="DYS464d"> DYS464d 10.3
<marker code="GAAT1B07"> GGAAT1B07 14
<marker code="YCAIIa"> YCAIIa 16.1
<marker code="YCAIIb"> YCAIIb 16.1
<marker code="GATAA10"> GATA-A10 14
<marker code="DYS635"> DYS635 21-22
<marker code="GATAH4"> GATA-H4 12
<marker code="DYS465X"> DYS464X 4
<marker code="DYS464f"> DYS464f 10.3
<marker code="DYS481"> DYS481 22
<marker code="DYS485"> DYS485 15
<marker code="DYS487"> DYS487 12
</markers></dnatest>
"
Extract of above pasted into PSPAD and then into text-formatted cells in Excel 2010:
DNA_Y-STR_markerslist_in_Excel.png

Discussions

ve3meo

Use a XML Viewer

ve3meo 03 September 2018 20:36:11

ve3meo Jun 18, 2011

Here's another way to view the XML data. Copy the Details cell contents to a text file and save it with the extension .XML; then open it with Internet Explorer or some other XML viewer for a color-coded and formatted view that is a big readability improvement.

Inline comments

ve3meo

Comment: Here's another way to view the XML da...

ve3meo 18 June 2011 19:22:00

Here's another way to view the XML data. Copy the Details cell contents to a text file and save it with the extension .XML; then open it with Internet Explorer or some other XML viewer for a color-coded and formatted view that is a big readability improvement.

www.000webhost.com