SQL XML :: How To Iterate Through Repeating Identical Child Nodes
Sep 16, 2015
I am working with a pretty complicated medical XML document and I am trying to query out lab information. The problem I am having is that there are many repeating child nodes and I can’t seem to get all the info I need without creating a bunch of columns. Here is a snippet of the XML; In attempting to shorten it up I may have left some elements unclosed but I hope not create table
I can grab all the lab names alright but I am tiring to also grab the lab components as well. A lab could have just one component or it could have a dozen and I will never know ahead of time how many there are.
The following query works ok but would like to be able to grab all the components for a given lab in one column instead of making several columns incrementing the singleton each time. Is there a way that I can iterate through without creating a bunch of columns?
Hi, I have a SQL statement that returns a hierachy
it returns ..
-NEWSPAPER -QUALS - TIMES - TELEGRAPH -MIDS - DAILY MAIL - EXPRESS - EVENING STANDARD - POPS - THE SUN
I want the statement to include childnumber column so for newspaper it would count the total number of children below it, so in the above example newspaper would be 9 and for the mids it would equal 3? and pops = 1. does anyone know how i can do this in SQL?
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
I have a custom essbase application/db source that I wish to connect to from SSRS 2005. I have all the required software installed (APS / SP2 .3050) etc.
I can connect to the datasource in SSRS 2005, but when I'm trying to build the query on the cube, I see under the member properties of all dimensions, the following piece of line "error occurred retrieving child nodes: null"
Also, when I try to drag my Measures into the query section (or any other dimension for that matter of fact, though that defies logic) I get the following error
Cannot perform cube view operation. OLAP error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query
I am new to using ESSBASE with SSRS 2005, and hence cannot make much sense of the above two errors. Any insight or a re-direction to similar posts with solutions will be greatly appreciated.
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |............. Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate) 2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate) 3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
although I don't consider myself a newbie in SQL, I find working with the date functions rather confusing.
I have this (very) simpel statement: Select count(*) from Invoices inv Where Date = '12/16/2003'
Which gives me the amount of invoices on this (exact) date. Is it possible to iterate between dates? I give a start- and enddate and it fetches the data for all the date in between ex. startdate 01/11/2003 stopdate 15/12/2003
Result ---------------------------- Date #invoices 01/11/2003 9 02/11/2003 5 03/11/2003 11 ... 14/12/2003 23 15/12/2003 21
Is there a way that I can iterate through the view and compare one record to the previous or next? (I didn't find anything in the Microsoft Library yet...)
I need to design this SSIS migration package to migrate data.
In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;
The SQL statement can be as simple as : select primary_key from a_table;
After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer
The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.
I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.
Anyone knows how to fix this?
I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.
Code Snippet CREATE PROCEDURE proc @param1 INT @param2 INT AS BEGIN
-- how to affect a variable to this resultset in order to iterate and get fields ? SELECT field FROM table END GO
In this proc, I would like to iterate through the resultset without using cursors, but I have no idea of the way to achieve it (I'm a very beginner in T/SQL)
And, in the case I would like to read this resultset through C# code, how can I return this resulset in my C# code in order to read it ?
hi i am having a hard time with two kinds of text files that have kind of 'repeating groups' in them...i want to loop it, but dont know how. one is a text file with a record length of 1200 bytes, but all 95601records are all on one row with no lf, cr or anything else between them, so i cannot feature how to get the forEach container to chop of a Right of claimchunk of 1200 bytes at a time, then go get the next 1200 bytes, because the items aren't stacked, they are adjacent to each other, if you see what i mean. the other text file has a record lenght of 52 bytes with 28 bytes filler, but this file also goes 'down and across', meaning that here, there are fourteen 'rows' in the file, and they have thousands of lines too, so this one also has to consume all the columns on the row before it moves to the next row. am i making this harder than it needs to be? thanks for any light
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
The following stored procedure works well as far as it goes but I need to amend it.
   @AE decimal (18,2) outputAS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2) (SELECT @odds= sum (1/ (RacingMaster.RM_SP) ) FROM RacingMaster where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data
and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server
like wise i have many xl files ( which have many sheets ) .
for eg:
excel file 1:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 2:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 3:
-> sheet 1
-> sheet 2
-> sheet 3
now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package
How does one setup a SSIS package to iterate through all (or selected) tables in a database. The reason is to update an internal database nightly using an approach similar to how Jamie Thomson stated in the below article.
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */ SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */ SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/
--Then somehow iterate each value in the field dbname to create a view something like Create View viewcreatedfromtable As Select * from foxfire.dbo.abcd Union All Select * from foxfire.dbo.abcde
[Code] ....
I tried this, but it is only printing the last result returned not the entire result set
Code: Declare @database varchar(max), @sql varchar(max) Declare c1 Cursor For Select Program from tocreateviewfrom Open c1
MS SQL 2008.I want to execute a delete query on certain tables in my database to delete some rows in the tables.The tables selected has a certain name pattern (the name ends with "Temp").
So I can do this to get a list of the table names
SELECT name FROM sys.Tables where name like '%Temp'
Now I want to check each table to see if it has a column with the name "DateStamp" and then execute a delete query as follows:
delete form TableName where DateStamp is < '2010-01-01'
In other words I need to iterate through the tables names, How to do this?
Another classic example of something silly I must be doing
I have a "Foreach Loop Container" which uses a "Foreach ADO Enumerator". The object source is a recordset variable.
Inside the "Foreach Loop Container", a lot of things are happening - some Execute SQL Tasks, Send Mail Tasks, Script Tasks and also a couple of Sequence Containers.
Now, I am getting 2 records in the recordset, but, the "Foreach Loop Container" executes just once.
I have a multi select param that I display on the title of the report using something like so: Parameters!Division.Label(0)
The problem is when a user selects more than one value it does not get displayed on the title. Addding Parameters!Division.Label(0) + Parameters!Division.Label(1) + Parameters!Division.Label(2) doesn't returns an error if user only selects 1 or 2 values because Parameters!Division.Label(2) is empty.
Is there a way to iterate through or do error checking on the array so that I can dynamically populate the title with the values selected by the user.
ID (int, identity) Name (nvarchar(255)) Block (nvarchar(50)) Street (nvarchar(255)) Floor (nvarchar(50)) Unit (nvarchar(50)) Address1 (nvarchar(255)) Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. Â So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
I have a list that is grouped by Department inside it I have a table with two columns: name and hours. For example:
Department A
Name Houres Mike 1
Department B
Name Houres Mike 1 Steve 1
Department C
Name Houres Mike 1 Steve 1
Outside the list I have another table that has a column for total hours for all departments: Which in this case is 5. Everything work fine, but I have problem with displaying the total hours for all departments. In other words this will be my output 5 5 5 5 5 5 5 5 5 5 5 5
It will be repeated multiple times. I believe I have to add group to the table that contains the column (total hours for all departments.) but I can€™t do that b/c It is the total for all departments. Also I tried to hide duplicate wich works, but the problem with that when I export it to pdf it will print empty pages.
I have a Client-Server - App where every Client-User has his own DB. The server is monitoring changes to all Client-DB's via SqlDependency. My problem can be reproduced with a small application, it even might be a €œfeature€? and not a €œbug€?:
- Consider two Databases TestDb1 and TestDb2 running on one SQL Server 2005 instance.
- Both DB€™s have identical Schemas.
- Consider the two DB€™s have each one table named €œTable1€?.
- Both tables have the same schema as already mentioned (the fields Id and Text).
- Now I setup a SQLDependency object on each Database:
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
If I make any changes to the Table in TestDb1 I get two notifications with the different Id€™s but the same Info,Source,Type (saying e.g. Data,Change,Update). If I make changes to the Table in TestDb2 I again get two notifications with the same result. As soon as I rename the Table in one of the Db€™s (e.g. Table2) and also change my Sql-Query in the code €“ I get just one Notification as expected. This behaviour is the same even If I change the connectionstring so that it points to another machine. So it somehow seems to fire a notification for every change to a table with the same name €“ regardless of the connectionstring where the physical change was done.
Does anybody know if this is a wanted behaviour of SqlDependency ? Does anybody know how I can set this up so I can have two DB€™s with identical Schemas and only get a Notification from the DB I actually changed ?
This is actually not asp.net, but I'm hoping someone can help me. I have a report that is pulling from a couple of different tables. I am trying to add a meal choice to the report. Let's say they have the option of choosing chicken, beef or fish. They check the checkbox next to the choice. My report is pulling the infor, but it is putting 3 rows for each person not taking into consideration what choice they chose. The checkbox's all write to the same column - ses. Here is the code for the stored procedure: CREATE PROCEDURE [dbo].[spGetCourseEmailList1]( @Code1 char(9)) AS SELECT DISTINCT dbo.[names].lname as LastName, dbo.[names].fname as FirstName,dbo.[evser].ses as MealChoice,dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firm INNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].id INNER JOIN dbo.evregON dbo.[evldg].id = dbo.[evreg].id Full OUTER JOIN dbo.evserON dbo.[evreg].code1 = dbo.[evser].code1 WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[evser].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGO The items in bold are what I added.
