Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 (2)
3 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

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

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;