Looping through each row in an XML object sent to a Stored Procedure

I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure.

Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.

The XML File is layed out as:

<Values>
<value>
<value>1</value>
<key>My_Field</key>
</value>
<value>
<value>3523.2</value>
<key>My_other_Field</key>
</value>
</Values
I basically need to go through it row by row, find out what table I need to insert the value into using thekeyfield.

Any help with this would rock. I'm using SQL 2005.

Tried a bit more but couldn't figure it out.

Basically I need to do something like this (logic):

Foreach row in @.xml

switch(SELECT FieldType FROM fields WHERE Name = @.Xml[key])

case :TextBox:
INSERT INTO TextFields(Value) VALUES (@.Xml[Value])
break;

case: listBox
INSERT INTO ListFields(Values) VALUES)@.Xml[Value])

... etc.

I have to check each row in the XML object sent in to see what table I need to insert the corresponding value to. I know this is possible, i'm just not sure how.

|||

Hi,

Actually you can use XMLDocument to get the data from XML file and store it into your database. See the sample below.

XmlDocument xd =new XmlDocument();xd.Load(inputurl);/// inputurl is the path of the xml file. XmlNodeList xnl = xd.GetElementsByTagName("value");for (int i = 0; i < xnl.Count; i++) { XmlNode xn0 = xnl.Item(i).ChildNodes[1]; XmlNode xn1 = xnl.Item(i).ChildNodes[0];string xn0_str = xn0.InnerText;string xn1_str = xn1.InnerText;// xn0_str stands for the table name // xn1_str stands for the value you want to insert. }

Hope that helps. Thanks.

|||

That would work, however that means I will be hitting the Database with many calls. I'd rather execute 1 call to the DataBase. Sometimes that XML file might have 100 rows in it. I don't want to hit the DB 100 times. I would rather send everything in at once, and then have the Database do all the work. It's less expensive that way.

Thanks :) I found a way to work it though using temp tables :)

|||

Hi,

Well, I know your needs. The main idea is to loop the xml file first, and take down each node's value in an array. And then build your insert statement dynamically by looping the array value.

Thanks.

Lookup Transformation: How can I join tables in different databases

I want to join tables that reside in different databases (same instance). The Lookup object only lets me select from one data source. Is there anyway to lookup using more than one data source? I can write a SQL query to lookup across databases.

Is this a feature that is being added to future releases?

I appreciate your help

-Marcus
Are these SQL Server databases? If so you can create a view in one database that selects data from another - thus making it appear as though the data is all in the same DB.

Voila!

-Jamie|||lol... didn't think of that one...

Thanks :)|||Still onthe lookup subject, I have noticed that if I write a simpley query in the box "User results of an SQL query" say select * from DB1.dbo.tablea, DB2.dbo.tableb, I can then click the "Build Query" button and hey presto both tables are then available for me to work with even thought they are from different databases. I do notice that the top left corner of the table boxes have an arrow.

However if I go straight to "Build Query", add my first table by right clicking and selecting "add table" there is no arrow in the top left corner of the table box. I can then modify the sql statment manually to include the table from the other database. This table then appears in the top window with an arrow in the top left corner of it's box.

Is this a bug? should there actually be an optin to add a table from an alternative database and it's missing? As detailed above I can manually add the tables and the tool recognisines them.

Has anyone else seen this? Has this been fixed in later builds? I'm using Junes.

Thanks|||

What you have observed is not a bug.

We do supply an option to add tables. But we only list tables in the current database context. Tables in other databases have to be added manually. This is the behavior in June CTP and are not changed since then.

|||

Do you know if this will be changed in future CTP's?

Thanks

|||

I do not think so.

But please feel free to open a DCR via BetaPlace.

|||What about MS Access using DAO? In DAO you use something like this.

CDaoRecordset rset(&db);
rset.Open(dbOpenSnapshot, SQLquery, dbReadOnly);

This does not allow for binding to more than one database at the time. Therefore, how can you do a join query between two tables residing in different databases?

Thank you.

Lookup error

Hi,

I'm using a Lookup object, but it dosn't works. Anybody knows this error?

[Lookup [29018]] Error: Row yielded no match during lookup.

[Lookup [29018]] Error: The "component "Lookup" (29018)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (29020)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (29018) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

It means exactly what it says. The pipeline value that you were looking up doesn't exist in your LOOKUP dataset.

If finding no match is a valid business scenario you can configure the component to ignore these errors. Edit the "Lookup Output" output which currently is set to "Fail component on error".

-Jamie

|||Thanks!