Parsing XML file

Turribeach

I have an XML file as shown below which I am loading as a dataset in Dataiku. The XML file shows the membership of each user in in each room. In the Format/Preview dataset screen I can either select /FullReport/rooms/room to get all the rooms or /FullReport/rooms/users to get all the users. But what I really want is to have the combined output as follows:

roomID	roomTitle	fullName
1	Room 1	JOHN DOE
1	Room 1	TOM DOE
2	Room 2	JOHN DOE
2	Room 2	JOHN SMITH
3	Room 3	SARAH SMITH

Any ideas how to do that in DSS?

<FullReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <rooms>
        <room>
            <roomID>1</roomID>
            <roomTitle>Room 1</roomTitle>
        </room>
        <users>
            <fullName>JOHN DOE</fullName>
        </users>
        <users>
            <fullName>TOM DOE</fullName>
        </users>
        </users>
    </rooms>
    <rooms>
        <room>
            <roomID>2</roomID>
            <roomTitle>Room 2</roomTitle>
        </room>
        <users>
            <fullName>JOHN DOE</fullName>
        </users>
        <users>
            <fullName>JOHN SMITH</fullName>
        </users>
        </users>
    </rooms>
    <rooms>
        <room>
            <roomID>3</roomID>
            <roomTitle>Room 3</roomTitle>
        </room>
        <users>
            <fullName>SARAH SMITH</fullName>
        </users>
        </users>
    </rooms>
</FullReport>

fchataigner2

You can do this in 2 steps:

- import the XML with format params:

- Root element XPath -> /FullReport/rooms

- Data extraction -> Explicit XPath

- extracted elements :

- ./room/roomTitle/text() -> roomTitle. (unique)

- ./room/roomID/text() -> roomID (unique)

- ./users/fullName/text() -> users

- use a Prepare recipe to fold the users array with the Fold processor

Turribeach

Hi, I played with it last night a bit more and I ended up doing the same thing you suggested so thanks for validating the road I took. It's a pity you can't extract the values into rows as part of the load as this can speed up the file load. But for my use case the file wasn't that big so the extra recipe didn't take that long. Thanks!