SQL Server 2008 :: Querying XML Data With Column Value In Same Select Clause
Aug 3, 2015
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked' FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd)) AS VARCHAR) AS daysworked, ActivityId AS totalcalls FROM PhoneCall AS p WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb) AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML SET @XMLVAR = '<?xml version="1.0"?> <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data from BF_DATA WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query**** ('A1', 'B1', 'C1') ('A2', 'B2', 'C2') ('A3', 'B3', 'C3') ('A4', 'B4', 'C4') ('A5', 'B5', 'C5')
but if i use the query below it will generate more records than these 5 records
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data from BF_DATA WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5') and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5') and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
Correlated Subquery: SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek), FROM dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
We have a database where the nvarchar columns currently holding English only data. It keeps the training information. Currently the default collation is Latin Case insensitive accent insensitive.
Now we are planning to allow multiple language support. When we go for it, we will upload the data from different languages. Based on user preferences, he/she should be able to query the data (One language at a time).
I am worried on how the existing queries will work if I load all language data in the same database.
For example, some characters in English are used in Norwegian too. But they have different sort order. ALso LIKE conditions too may fail.
So far I thought of few solutions: Add the Collation information along with select , order by clauses. It means we need to add more procedures (one set per collation) The other option is to create new database for each language. Each will have its own collation. The dowside is we need more databases which may lead to more servers and more maintenance work.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column: thisisavalue thisisanothervalue thisisanothervalueagain shortval
replaced with xxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx xxxxxxx
My current function is replacing the data like this: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
I was wondering if there was a way to redirect an insert to another column...
Example: Original Insert Statement: INSERT INTO [table] ([columnA], [columnB]) SELECT '2015-01-01 00:00:00', 99.99
We have changed [columnB] from a decimal(19,9) to a computed column. So instead, we added another column [ColumnC] to take [ColumnB]'s insert data. I thought we could've used a trigger instead of insert, but that fails with the message "cannot be modified because it is either a computed column or is the result of a UNION operator".
This is the trigger I was using: CREATE TRIGGER [Trigger] ON [table] INSTEAD OF INSERT AS INSERT INTO [table] ([columnA], [columnC]) SELECT [dataA], [dataB] FROM Inserted
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL] CREATE TABLE [dbo].[PARAMEXAMPLE]( [Entity] [varchar](16) NULL,
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
HI, I'm trying to implement a site search. The only problem is that some of the pages have more than 8000 characters (like the press release pages) so I had to use the TEXT datatype (sql server 2000). I wrote a simple stored procedure to query the column, however it does not return any results. I'm assuming this is due to the fact that it is the TEXT datatype. ANY help would be GREATLY appreciated. Code below: CREATE PROCEDURE sp_SiteSearch @keyword text
AS
SELECT PageText FROM tbl_Pages WHERE PageText LIKE '%@keyword%' GO
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor) FROM SensorData WHERE SensorID = @SensorID AND StartTime + Offset > @BeginTime AND StartTime + Offset < @EndTime GROUP BY (StartTime + Offset) / 100 ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages: Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
'Banquets - All Day' as revName, SUM(t.c_items_total) AS Banquet_Total, SUM(t.cover_count) as Total_Covers, -- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--
[Code] ....
The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.
Hi.I bet this is a 101 question, but i'd appreciate any help!I am in the 'where...' section of the configure data source wizzard .Column: I grab 'UserName' Operator: I select '='BUT how do I get the UserName (The user is signed into the app) Is it from the Form? Profile? Session?Ive tried profile.name..... THANKS In advance.... Dan
While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code> select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate from ( SELECT database_id,[Database Name]= DB_NAME(database_id), [Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)' WHEN Type_Desc = 'LOG' THEN 'Log File(s)' ELSE Type_Desc END ,
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
