In MS Access database, the Occurrence table has an XML data
type field called “ReportDataXML”. This field contains details/values for all
the fields on a particular form for a given record.

I need this field transposed to a table for each record in
the Occurrence table. The transposed table should have columns for record id
and the xml values for each node – see second table for an example of what I’m
after.

 

Source data sample – record id = 6, ReportDataXML field extract only for 3 fields.

Occurrence table ID field has value:

6

ReportDataXml field has the following content (exerpt only):

<F7183047af02945f1aa4cf819519c1b72 xmlns="http://www.gaelquality.com/Bacchus/OccurrenceFields" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 

<DataType>list-reference</DataType>

 

<Value>3193</Value>

 

<Archived>false</Archived>

 

</F7183047af02945f1aa4cf819519c1b72>

 

<Fa080141ebf5b417999a0b5da90569306 xmlns="http://www.gaelquality.com/Bacchus/OccurrenceFields" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 

<DataType>string</DataType>

 

<Value>Garry Capp</Value>

 

<Archived>false</Archived>

 

</Fa080141ebf5b417999a0b5da90569306>

 

<F824a8279f81a4eeaba0e3b6ac62bbccd xmlns="http://www.gaelquality.com/Bacchus/OccurrenceFields" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 

<DataType>string</DataType>

 

<Value>The employee, received an injury to their shoulder and neck whilst using a crowbar to remove rocks &amp; concrete.

 

 

INC allocated to wrong work area. Found INC report and attached relevant docs that actions had been completed. S Gueth</Value>

 

<Archived>false</Archived>

 

</F824a8279f81a4eeaba0e3b6ac62bbccd>

 

Expected output from transpose would be:

ID

UIElement

DataType

Value

Archived

6

F7183047af02945f1aa4cf819519c1b72

list-reference

3193

FALSE

6

Fa080141ebf5b417999a0b5da90569306

string

Garry Capp

FALSE

6

F824a8279f81a4eeaba0e3b6ac62bbccd

string

The employee, received an injury to their shoulder and neck whilst using a crowbar to remove rocks &amp; concrete.

 

INC allocated to wrong work area. Found INC report and attached relevant docs that actions had been completed. S Gueth

FALSE

 

Could you please guide me with the steps please?