CODEWITHSUNDEEP
xmlssis
Sziro
Sziro

Reputation: 1303

Getting data from XML in SSIS

I have the following XML:

<?xml version="1.0" encoding="UTF-16"?>
<APIDATA xmlns="api-com">
<ORDER EngineID="1" OrderID="66"><INSTSPECIFIER InstID="27" SeqID="17"/></ORDER>
<ORDER EngineID="2" OrderID="67"><INSTSPECIFIER InstID="28" SeqID="18"/></ORDER>
</APIDATA>

I would like to get all IDs to SSIS variables in a for each loop for all Order entries. So far I can get the first two id with a ForeachLoop in control flow in SSIS, with the following:

EnumerationType:  ElementCollection
OuterXPathString: //*[name() = 'ORDER']
InnerElementType: NodeText
InnerXPathString: @*

Then on variable mappings I use two string variables with index 0 and 1, to get the two IDs. How can I get the INSTSPECIFIER element's ID? What would be the correct XPath command for getting all four IDs in one go?

Note, there can only be one INSTSPECIFIER in the ORDER element. Also in case this needs to be done seperately, like getting the ORDER in the ForeachLoop and getting the INSTSPECIFIER in an XMLTask, the OrderID is the primary key. So in this case, the question is, how can I get in an XMLTask the InstID and SeqID of a INSTSPECIFIER entry, that has a parent ORDER entry with a specific OrderID?

Thanks for your help.

Upvotes: 0

Views: 676

Answers (1)

Piotr Sobiegraj
Piotr Sobiegraj

Reputation: 1783

This time try four variables and InnerXPathString: @* | child::node()/@* Other values without change.

Upvotes: 1

Related Questions