How do I access an attribute from a root node using xml source
-
Hi,
I am new to SSIS.
I am trying to import large xmls into a database. For a simple example consider the following xml:
<objects importMode="add">
<object objectNumber="42272" name="Langness" />
<object objectNumber="33283" name="Fuhrmann" />
<object objectNumber="81651" name="Blain" />
</objects>
In the data flow, I used a XML Source and an OLE DB Destination. I managed to get the above data into the objectnumber and name columns of the correct table in the database. However, I need to retrieve the importMode as it dictates whether I am updating/inserting or even deleting records from the database. How can I access this attribute? I cannot see any option to do this with XML Source object.
Any ideas?
Thanks
Jose
-
Start with an "Xml Task" (maybe nested within a ForEach File Enumerator if you have many files to process). Here's how my Xml Task is setup:
Operation Type: XPath
Source Type: File Connection
Source: MyFile.xml (this is a File Connection in the Connection Managers which is dynamically updated in the ForEach Loop)
Save Operation Result: True
Destination: User::MyNewXml (Variable of your choice)
Destination Type: Variable
OverWriteDestination: True
SecondOperandType: Variable
SecondOperand: User::MyXPath (Variable that stores the Xpath or Xsl to parse the file)
PutResultsInOneNode: True (this wraps all the returned nodes in a Root Node - this is necessary if your using XPath)
XPath Operation: Node List
Now, I use a "Foreach NodeList Enumerator" to enumerate each Node. Here are my parameters:
DocumentSourceType: Variable
DocumentSource: User::MyNewXml (Variable from the previous Xml Task where the XPath is storing the results)
Enumeration Type: Element Collection
OuterXPathSourceType: Variable
OuterXPathstring: User::MyXPath (here I used the same xpath, this might change depending on if you're using Xsl)
InnerElementType: NodeText (if you want to retrieve Attributes from your Node, use "Node" here instead)
InnerXPathSourceType: DirectInput
InnerXPathString: . (yes, just a dot)
Now, after you've configured all this, goto the Variable Mappings (of the For loop) and map each Attribute to a Variable by specifying the Index of each attribute (Index is 0 based).
That should do it.
-
Hi,
Thanks for the tips.
I end up trying something very similar. I am using a XML task to retrieve the attribute of the root node and storing that in a variable.
I was then trying to use a Precedence Constraint which tests for success of the XML task and for the variable. This would allow me to have different data flow tasks depending on the value of the variable (ie the value of the root node of the xml file).
However I am having trouble testing the contents of the variable. Maybe it is a timing issue or something but the xml task appears not to write the attribute value to the variable. The test always equates to false!
Is there anyway to test this variable to decide which data flow task to follow?
Cheers
Jose
-
Hi,
Further to the post above, I have confirmed that he XPATH command is doing the correct thing. If I set the Operation Result Destination to be a file, then I can see the correct thing being written to the file, ie, the xml attribute I need to parse.
But if I set a variable (type string) as the Operation Result Destination then the variable does not seem to be affected. At least in the variable view it remains empty or whatever it is currently set to (I have set the OverwriteDestination to true).
So the question is, can a XML task output a result into a variable? If so is there anything special that needs to be done?
Cheers
Jose
-
One thing about SSIS is that you can pretty much NEVER see the variables being updated. You just have to assume everything went well. If you really want to see it, insert a Script Task that uses a MessageBox to display the variable.
System.Windows.Forms.MessageBox.Show(Dts.Variables("MyVar"))
Do NOT put "User::MyVar", it's only "MyVar".
-
Hi,
Yes, I realised that the hard way!
Actually, the variable was being updated but if you then compare with a certain string you might not get exactly what you expect because of control characters it adds. I think that this was the major issue.
You can also see the contents of variables by using watch and breakpoints.
I have since been battling to get the Script Tasks to work on my Vista environment. After some pain and hotfixes applied it now all seems to work.
Steep learning curve ahead .....
Thanks very much for the help!
Jose
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply