Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shirleyc40
Creator
Creator

XML Column in SQL table

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 

IDDateXML
16/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:

IDDateeventNamelocationcost
16/22/2020E1New York$100.00

 

Thank you

Labels (3)
3 Replies
fosuzuki
Partner
Partner

Hi,

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

shirleyc40
Creator
Creator
Author

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
Partner
Partner

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;