XML Column in SQL table

[shirleyc40]

Hi

I am loading data from a SQL table, and one of the columns is an XML link that opens up to XML . I was wondering how I can parse the data from the XML column so that it becomes extra columns. For example:

If one of the SQL table rows was 

ID	Date	XML
1	6/22/2020	...

Where the XML is:

<EventRecord>
<Header>
    <eventName>E1</eventName>
</Header>
<eventDetails>
    <location>New York</location>
    <cost>$100.00</cost>
</eventDetails>
</EventRecord>

How can I make it so that the resulting loaded table looks like:

ID	Date	eventName	location	cost
1	6/22/2020	E1	New York	$100.00

Thank you

[fosuzuki]

Hi,

take a look at this other thread, see if you can understand the solution proposed by @rwunderlich .

[shirleyc40]

When I try this, it only creates a separate table that contains 1 row. Like if the SQL table had two entries, each with its own XML, the XML table that returns is just 1 entry.

[fosuzuki]

The idea is to have something like this:

[Data]:
LOAD rowno() as Seq,
	[ID],
	[Date],
	[XML];
SQL Select [ID],
	[Date],
	[XML]
FROM SourceTable;

tmp:
load rowno() 					as Seq,	 
	[eventDetails/location] 	as eventDetails.location,
	[eventDetails/cost] 		as eventDetails.cost,
	[Header/eventName] 			as eventName
From_Field (Data, XML) (XmlSimple, Table is EventRecord);

Left Join(Data)
Load * Resident tmp;
Drop Table tmp;