tXMLMap loop inside loop

[michaelh1]

Hello,

I am working on job that will parse my xml and save it to multiple DB tables using tXMLMap and tDBOutput. My problem is that in tXMLMap i cant set loop element inside loop element example:

<author>
   <book>
    <chapter/>
    <chapter/>
   </book>
   <book>
    <chapter/>
    <chapter/>
   </book>
</author>

(as you can see i have loop of books that contains loop of chapters)

What is the best practice to parse such an xml?

[Anonymous]

Hi,

You can set loop in tXMLmap in a column if your datatype is document.

First, define  any column in txmlInput and give xpathquery as "." also check the getNodes button

In the tXMLinput define the datatype of the new column as a document. Then right-click on the column and import structure from the XML file. Then set the loop in the column you want.

Don't forget to give kudos/accept the solution when a replay is helpful.

Thanks,

Aarif

[michaelh1]

Hello,

i know how to use single loop in xml document. But what i need is to iterate all the chapters (loop of chapters) in each book. So basically what i need is to iterate loop of chapters in the loop of books.

[Anonymous]

Hi,

Basically you want to loop in the chapters. For that, you need to insert data inside all the chapter tag and then loop in the chapter. You will be able to loop into it.

[michaelh1]

Yes you are right i need to loop all the chapters and store it to DB, but i also need to get the parent elements of element chapter author and book. lets say that my xml looks like this:

    <author>    
        <name>Author1</name>
		<books>
			<book>
				<name>Book1</name>
				<chapters>
					<chapter>
						<name>Chapter1</name>
					</chapter>
					<chapter>
						<name>Chapter2</name>
					</chapter>
				</chapters>
			</book>
			<book>
				<name>Book2</name>
				<chapters>
					<chapter>
						<name>Chapter1</name>
					</chapter>
					<chapter>
						<name>Chapter2</name>
					</chapter>
					<chapter>
						<name>Chapter3</name>
					</chapter>
				</chapters>
			</book>
		</books>
    </author>

And i need to store to table Chapter with attributes chapter_name, book_name, author_name. So in this case i should have 5 rows in my table:

Chapter1|Book1|Author1

Chapter2|Book1|Author1

Chapter1|Book2|Author1

Chapter2|Book2|Author1

Chapter3|Book2|Author1

If i will just put loop on chapter element i will get al the chapters but i wont get info from which book and author it was.

[Anonymous · Accepted solution]

If you give the loop in the chapter then all the attributes will come.

And the output you will get

Don't forget to give kudos/accept the solution when a replay is helpful.