Combine SQL query result with XML query result

Hi all,

Hopefully someone can help me with this: I know how to setup and run a query on an SQL db and I can probably work out how to query an XML file, but how do I take the results of each and combine them into one collection (i'm presuming a dataset)? Once i've created the combined collection, i'd want to order it by some universal identifier present in both datasources e.g. product ID. It's worth pointing out that the XML file would not be in the same format/use the same field names (or number of fields) as the SQL query, so I guess I might need to bind my query results to a dataset as per usual, but then do a more complex bind with my XML result that maps the XML fields to the ones in the query result (and therefore the dataset). To make things very slightly more difficult, I'd also like to add an extra field to the dataset that specifies where each row came from i.e. "SQL" or "XML"...

 Any help is much appreciated!


Ramic Solutions
0
Screamer
3/3/2008 11:29:21 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

4 Replies
913 Views

Similar Articles

[PageSpeed] 15

Basically, you are on a rigth track. But, the easist way to merge two data tables into a dataset, you have got to make the table columns have the same name. My suggestion is that becasue you probably have the limitation to change the XMl file node name, you better change SQL results name to fit XML. For example, in XML, you have a ProductId, in DB, you have a ID, what you can do is

SELECT Id as ProductId.

Do all columns to fit XML file.

After two data tables generate, you can do DataSet merge to put two tables in one dataset.

Hope it gives you an idea.


"Object reference not set to an instance of an object"
0
che3358
3/3/2008 3:34:03 PM

Thanks che3358! Only problem with that method is that I'm not able to add my marker field to the eventual dataset that specifies whether each record came from the SQL query or XML file.

To achieve that, am I going to have to read both the SQL and XML into a dataset 'manually' row-by-row, and then sort the dataset?

Thanks for the help so far!


Ramic Solutions
0
Screamer
3/3/2008 3:44:16 PM

Not sure for that, but can you try to put a trick on DB side like SELECT ...., 'SQL' AS DataResource.

So, now in your SQL data table, you have a column called DataResource, but it doesn't exist in you XML table. In this case, if you do merge, guess what happen for XML table side? Hopefully, you will get null for it. If it works, you can easily find which data source the data comes from.

Just some thoughts ever though I didn't try before.


"Object reference not set to an instance of an object"
-1
che3358
3/3/2008 3:58:48 PM

Thanks again che3358. Your method did cross my mind, but i'm just thinking that in the future, I may want to incorporate more than 2 SQL queries and/or XML files, so would really need to label each and every record, rather than just let some be null. Thanks for your help anyway - sounds like I would need to manually read records from both SQL and XML and write them into a custom dataset... Or read the XML file(s) into the database so that everything comes from there, of course.


Ramic Solutions
0
Screamer
3/3/2008 4:26:28 PM
Reply:

Similar Artilces:

Novice DBA looking for Advice on Querying the result of a query of a results of a query of a....
Many thanks in advance! Here's an example of what I'm working on.  Let's say I have a row of data that is returned in a query.  The columns are First, Middle, and last.  We'll say the values in each column, respectively, are 1, 2, and 3. So I run my first query, something akin to SELECT MIDDLE WHERE MIDDLE = 2.  And we get that row.  Excellent. What I'm trying to do, is after that statement is executed, run another one on the webpage that looks like SELECT LAST WHERE LAST = [the value that was returned in the previous statment]. And the k...

Search query returns XML results. How do I get the results back to the page that submitted the query or a new page.
Hello,I have a search page with a text box and button. When I search for a keyword, my page sends parameters to a search appliance that returns XML results. How can I get the results back to my search page or some other page on my site and not the search appliance. I know I need xlst to transform the XML results but, how do I get the results back on my site instead of the appliance on by just entering a keyword and clicking on the search button. Thanks for any help! I'm assuming that you are doing a direct form based post to the search appliance which is a service taking a keyword and retu...

AutoComplete results not the same at sql query results
For some reason my AutoComplete web service is not return the same data set as when I run the query manually. For example, here is my Oracle query and the results: p_value = 'HOME DEPO'; SELECT TRIM(spropertyname) spropertynameFROM usaa_acqpropertiesWHERE spropertynamecaps LIKE (p_value || '%') Returns:Home DepotHome Depot (Winchester, VA)Home Depot - Colma Using the AutoComplete I only get one entry:Home Deposition Sciences, Inc.Here is my webservice code: [WebMethod]public string[] AutoCompletePropName(string prefixText, int count){string prefixTextUpper = prefixText.T...

If one query returns any result then the result from other query should be eliminated..
HI, I want to create a query which returns allowed reports for the user. There are two different queries which returns the allowed reports. One returns the reports according to user and other reutrns reports according to the role of user. e.g.Select ReportID from UserReports and  select ReportID from RoleReports (in original scenario both queries are quite complex and large) Requirement is if the first query returns any row then the result of the from the second query should be ignored. IF(Select ReportID from UserReports > 0) Begin Select ReportID from Us...

How To Combine 2 SQL Query Results Into One In Order To Bind to a Gridview Datasource?
Hi, Scenario: In code behind (.cs), I have to perform two seperate sql queries. For the simplicity, First I need to select the CustomerName from TableA. then another query to select CustomerPhone from TableB. Note that there IS NO join between these two tables. How can I combine these two resullts so that I can bind it to a single gridview datasource? Any suggestion for a simple solution.. ? Do your tables have only one column?  Iterate through tableB and copy all rows to tableA...  http://support.microsoft.com/kb/305346 It could also be done at...

Querying a Queried result...
Hi, is there such a thing as querying a queried result? Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B If there is, could you kindly direct me to a website with the appropiate article on this topic? Thanks, -Gabian- Yes there is, querying a View is querying a query rewrite. Send me an email the address is in my profile I will send you a Views tutorial I wrote a while back and run a search for query results from View in your BOL(books online) for code samples. Hope this helps. Kind regards, ...

can i do an sql query then use the result of that in another query...
for instance i need to find value of storyId from one table then make sure i do not pull that record from another table. is this possible With SQL you can do something like this: select * from TableB where StoryIdValue not in (select StoryIdValue from TableA) This is just 1 of many possible solutions. ChrisDont forget to click "Mark as Answer" on the post that helped you.This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. Yes this is possible. This is what is called a "Nested Query" in SQL. ...

Easy SQL question. How to display query results in Query Analyzer
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare     @SniierId as   uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare    @SniierAlias  as nvarchar(50)declare    @AlwaysShowEditButton  as bitdeclare     @SniierName  as  nvarchar (128)/* Check access for Snii...

VB .NET Dataset SQL query results
Hi, I'm hoping someone can shed some light on how I can address variables returned to a dataset via a stored procedure.I've coded an AJAX Autocomplete control but due to the restriction of the control (only being able to return one value) I now need to use the returned value to search my DB.  The stored procedure used for the Autocomplete returns the key information (and the description).  I'm assuming the key info is stored within the dataset. So my question is, how do I read the key information into a variable for use elsewhere?Stored proc Select Statement; SELECT SU...

Query a Query with multiple results
Hi,New to .Net and SQL.  I have two tables that I have joined together.  RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name.  I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts.  I have a details report that gives each contract info. for each specific adjusters rentals.  However, I want to just list the adjuster once and give all of their totals.  In my SQL statement I have all of it written out and just need to knowwhat to do in pla...

vb.net code dependent on sql query results
Hi, I was wondering if someone can help. My apolgies if this is posted to the wrong forum. I am trying to do some vb.net code around the results of excecuting a sql query. The sql query compares entries for a project budget and items invoiced (actuals) for a specific project task (the sql join is on the TaskId). When executed it returns either one or many records. Each record has the following info (in fields): a budget rate (e.g €1000), budget qty (e.g 2 man days), budget total (budget qty x budgetrate), task ida actual rate, actual qtry, actual total (actual qty x actual rate...

weird difference between t-sql and ado.net query results
Hi All: I have a really simple query that returns a recordset with a count for each row...if I run the query in an SSMS query, I get one set of results, but if I run the query in my code-behind, I get different results? I don't know how that could be possible (except I'm making an obvious mistake somewhere)? In SSMS I have: SELECT     s.radiation_dose, COUNT(s.radiation_dose) AS Expr1FROM         table_procedures AS s INNER JOIN               &nbs...

performing percentage calculation in SQL query results & checking for 0 & rounding down of calculation result
hiI got SQL query which return some numbers like this:select num1, num2 from tblnumi want to calculate % value like so:select num1, num2, total = (num1 / (num1 + num2)  * 100 ) from tblnum  The problem is that:1. SQL seem to round DOWN num1 / (num1 + num2) ....so everything becomes 0 ( I can do num1 * 100 / (num1 + num2) to get the percentage but it still I think get rounded down)2. How do I write nice SQL code to handle div by zero error when (num1 + num2) is 0? thanksS        1, SELECT (CAST (@num1 AS FLOAT)/(@num2 + @num3) *100) AS res&n...

Sql Query Results #?
I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer) select TotalReturned from table where field="" 1. select TotalReturned=@@ROWCOUNT from table where field="" 2. select TotalReturned=COUNT(*) from table where field="" i'd recommend 1. hth ***********************Dinakar NethiLife is short. Enjoy it.*********************** If you're selecting then use the COUNT aggregate Se...

Web resources about - Combine SQL query result with XML query result - asp.net.object-datasource

Combine harvester - Wikipedia, the free encyclopedia
The combine harvester , or simply combine , is a machine that harvests grain crops. The name derives from its combining three separate operations ...

Facebook Marketing Companies Combine As Brand Networks Acquires Optimal For $35M
Integrated social software solutions and digital marketing services provider Brand Networks announced its acquisition Wednesday of Optimal , ...

Facebook Combines Most Recent and Top News Into a Single Feed, Adds a Real-Time News Ticker
Facebook just announced that it is redesigning the home page’s news feed to combine the Top News and Most Recent feeds. Rather than having to ...

Pit Crew Combine for iPhone, iPod touch, and iPad on the iTunes App Store
Get Pit Crew Combine on the App Store. See screenshots and ratings, and read customer reviews.

Combine working at night - Flickr - Photo Sharing!
Passed this on the way home from the Tornado train shoot. 6400 ISO resting on a roadside barrier.

2014 NBA Combine: Zach LaVine Turns Heads At NBA Combine - CampusInsiders - YouTube
Several top NBA prospects opted out of participating in the draft combine this year giving others a chance to shine. Campus Insiders' host Seth ...

Educator combines experience, drive and passion to help school pupils succeed
Muna Saqir Al Matroushi started teaching at the age of 18 and since then has devoted her time, money and effort into helping children develop, ...

Mashup of the Day: Todrick Hall Combines ’90s Pop Hits & Disney Songs
Remember that time Montell Jordan flew around on a magic carpet with a genie who granted him any wish his heart desired, all the while singing ...

Don’t combine chaplains and welfare workers
Our primary school is undertaking a community “consultation” over the appointment of a school chaplain versus a secular student welfare worker. ...

AFL draft combine harvester: tall forwards
Emma Quayle's guide to the 100-plus players invited to the AFL Draft Combine 2014. The draft expert looks at the tall forwards.

Resources last updated: 11/25/2015 12:02:27 AM