NameSpaces in xml sql

How can I declare the namespaces in a query? I get an error when declaring the namespaces Declare @Mydoc xml set @Mydoc = ( select importe, algo,XD, (select impuesto,tasa,transferencia from CfdiDet ...
more »

2017-11-29 18:11 (1) Answers

Convert Oracle query to SQL Server 2008

I am facing a problem to convert an Oracle query to its SQL Server 2008 version. In this Oracle query LISTAGG and connect by prior are used. I researched the web to find that WITH AS () is connect by prior equivalent in SQL Server 2008. Also for LI...
more »

2017-11-19 19:11 (1) Answers

Reserved Keyword in Where clause

Consider the following setup create table #test([user] varchar(10)) insert into #test([user]) values ('test') when user column is enclosed with square brackets then the query returns result else it does not return result even though there is a mat...
more »

2017-11-17 15:11 (2) Answers

Rank records in sets based on multiple fields

╔════════════════════╦═══════════╦══════════╦═══════╦══════╗ ║ ENDTIME ║ TRAILERID ║ POSITION ║ STORE ║ STOP ...
more »

2017-11-07 23:11 (1) Answers

How to Select distinct values in FOR XML PATH?

Given the following tables where T_DATA.ID = PARENT_ID or CHILD.ID Name: T_DATA +----+------+--------+ | ID | CODE | VALUE | +----+------+--------+ | 1 | 3186 | value1 | | 2 | 3186 | value2 | | 3 | 3189 | value3 | | 4 | 3189 | value4 | | 5 | ...
more »

2017-10-31 17:10 (1) Answers

Get part of the string between 2 different strings

I'm using SQL-Server 2008 R2. First of all, I want to tell you that's I know that store strings like this is super bad practice, but as I'm SQL developer I don't have an ability to change it, the software of third-party generating output and inserti...
more »

2017-10-25 10:10 (3) Answers

Using column value as column name in subquery

I'm working with a legacy DB that has a table that houses field names from other tables. So I have this structure: Field_ID | Field_Name ********************* 1 | Col1 2 | Col2 3 | Col3 4 | Col4 and I need to pull a list of this field met...
more »

2017-10-10 20:10 (1) Answers

Eliminating rows by date range

I have the following: Index dateOfInquiry 649454 2016-02-05 649455 2016-02-05 And I've got this query: SELECT COUNT(a.dateOfInquiry) as NumberRecords FROM ( SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC) ...
more »

2017-10-09 22:10 (2) Answers

Case Statement Based on 3 tables MSSQL

I need to write a case statement based on the 3 tables such that if column 1 from table A is blank then look for column 2 from table B and if column 2 is blank then look for Column 3 from table C TableA Column 1a Column 1 1 TableB Col...
more »

2017-10-03 17:10 (2) Answers

SQL Server row_number() Partition by query

I have my data in below format and trying to get unique customerIDCount using and accountCategoryount using row_number() and partition by but having issue with below query , can anyone pls help ? with cte as ( select *, account...
more »

2017-10-01 22:10 (1) Answers

Retrieving the only value from XML string

Here is a my XML string of datatype nvarchar(MAX): declare @string Nvarchar(MAX) set @string='<ROOT><Data C="1" /><Data C="2" /><Data C="3" /></ROOT>' Expected result: @c='1,2,3' i want to check that the value of...
more »

2017-09-27 12:09 (2) Answers