extract XML

[vick]

Hi , From IE i got below XML content. How i can extract into excel or table pls?

<xml id=RunQuery>
<list count="2"><fmt><v id="servercol" n="0" type="unknown" /><v id="workitem_number" n="1" type="int" /><v id="workitem_title" n="2" type="string" /><v id="assigned_to_worker_name" n="3" type="string" /><v id="workitem_priority_lookup_ordinal" n="4" type="int" /><v id="workitem_priority_lookup_value" n="5" type="string" /><v id="workitem_status_lookup_ordinal" n="6" type="int" /><v id="workitem_status_lookup_value" n="7" type="string" /><v id="workitem_category_tree_value" n="8" type="string" /><v id="workitem_modified_on" n="9" type="datetime" /></fmt><i n="0"><v /><v>487375</v><v>ON - Test eLabLink account request for: DR. PICHE 165472</v><v>Level 2 Test eLablink Registrations</v><v>3</v><v>Medium</v><v>12</v><v>Open</v><v>Test\eLabLink Web Portal\Registration Request</v><v>10/12/2017 2:34:47 PM</v></i><i n="1"><v /><v>487792</v><v>ON - Test eLabLink account request for:DR. SYLVESTRE  </v><v>Level 2 Test eLablink Registrations</v><v>3</v><v>Medium</v><v>12</v><v>Open</v><v>Test\eLabLink Web Portal\Registration Request</v><v>10/17/2017 11:06:53 AM</v></i></list></xml>

[Andreik]

And how should look the final excel?

[mikell]

Very basically it could be this 

#Include <Array.au3>

$s = FileRead("test.xml")
Local $res[0][9]
$s = StringStripWS(StringRegExpReplace(StringReplace(StringReplace($s, "</v><v>", "|"), "<v /><v>", @crlf), "<.*?>", ""), 3)
_ArrayAdd($res, $s)
_ArrayDisplay($res)

 

[vick]

i got everything null in all columns value.

 

[vick]

if i arrange in orde rproperly then looks like below:

<?xml version = "1.0" encoding = "UTF-8" standalone = "no" ?>
<xml id=RunQuery>
<list count="2">
<fmt>
<v id="servercol" n="0" type="unknown" />
<v id="workitem_number" n="1" type="int" />
<v id="workitem_title" n="2" type="string" />
<v id="assigned_to_worker_name" n="3" type="string" />
<v id="workitem_priority_lookup_ordinal" n="4" type="int" />
<v id="workitem_priority_lookup_value" n="5" type="string" />
<v id="workitem_status_lookup_ordinal" n="6" type="int" />
<v id="workitem_status_lookup_value" n="7" type="string" />
<v id="workitem_category_tree_value" n="8" type="string" />
<v id="workitem_modified_on" n="9" type="datetime" />
</fmt>
<i n="0">
<v />
<v>487375</v>
<v>ON - Test eLabLink account request for: DR. PICHE 165472</v>
<v>Level 2 Test eLablink Registrations</v>
<v>3</v>
<v>Medium</v>
<v>12</v>
<v>Open</v>
<v>Test\eLabLink Web Portal\Registration Request</v>
<v>10/12/2017 2:34:47 PM</v>
</i>
<i n="1">
<v />
<v>487792</v>
<v>ON - Test eLabLink account request for:DR. SYLVESTRE  </v>
<v>Level 2 Test eLablink Registrations</v>
<v>3</v>
<v>Medium</v>
<v>12</v>
<v>Open</v>
<v>Test\eLabLink Web Portal\Registration Request</v>
<v>10/17/2017 11:06:53 AM</v>
</i>
</list>
</xml>

[mikell]

Well, just remove the newline sequences

#Include <Array.au3>

$s = FileRead("test.xml")
$s = StringRegExpReplace($s, '\R', "")
Local $res[0][9]
$s = StringStripWS(StringRegExpReplace(StringReplace(StringReplace($s, "</v><v>", "|"), "<v /><v>", @crlf), "<.*?>", ""), 3)
_ArrayAdd($res, $s)
_ArrayDisplay($res)

 

[vick]

thanks it is working now.