How to get values from the xml value without mentioning its attribute's name in select statement

The below will works fine. But I want to read it dynamically without attributes or coulmn name Sample xml declare @xmlval xml='<ArrayOfCustomers> <Customer> <ItemId>1</ItemId> <Value>Mr Smith</Value> </Customer> <Customer> <ItemId>2</ItemId> <Value>Mr Bloggs</Value> </Customer> </ArrayOfCustomers>’ Sample query SELECT Cust.value(‘(ItemId)[1]’, ‘int’) AS ‘ItemID’, Cust.value(‘(Value)[1]’, ‘Varchar(50)’) AS ‘Customer Name’ FROM @xmlval.nodes(‘/ArrayOfCustomers/Customer’) AS AOC(Cust) The above works fine. How to […]

XML SQL query Output format issue

My SQL query is: select MedicalHeightValue, MedicalWeightValue from TableA My column names & values are: MedicalHeightValue(67), MedicalWeightValue(220) Output should be like: <HealthAttribute> <Identifier>MedicalHeightValue</Identifier> <Value>67</Value> </HealthAttribute> <Healtttribute> <Identifier>MedicalWeightValue</Identifier> <Value>220</Value> </HealthAttribute>

automatically pivot data in table using sql server

I have store procedure like this: ALTER procedure [dbo].[performance] @startdate nvarchar(100), @enddate nvarchar(100) as begin declare @date1 nvarchar(100)=convert(varchar, @startdate+’00:00:00.000′,120) declare @date2 nvarchar(100)= convert(varchar, @enddate+’23:59:59.000′,120) set NOCOUNT on; select l.LocName,v.Vtype, SUM(convert(numeric(18, 2), DATEDIFF(MI,t.DelDate,t.Paydate))) as TotalDiff, [dbo].[testfunctionstacknew] (CONVERT(decimal(10,1), AVG( CONVERT(NUMERIC(18,2), DATEDIFF(SS,t.Paydate,t.DelDate) ) )) ) as Average from Transaction_tbl t left join VType_tbl v on t.vtid=v.vtid left join […]

Unable to execute stored procedure but the update statement within the stored procedure works

I have a database that is currently replicated to three different locations. It is used for an application and all tables, stored procedures and articles are selected to replicate as well. When I try updating the ContactCSR, through the stored procedure, I get the following error: Msg 2627, Level 14, State 1, Procedure UpdateContactCSRs, Line […]

SQL query to Find a user hits (or visits) more than two times in last day

What is the best way to find out that the count of user visits are more than two time in last day? I’m using this for getting total count of them: SELECT COUNT( DISTINCT [UserID] ) FROM UserHit WHERE DateOfHit BETWEEN DATEADD(day, -1, convert(date, GETDATE())) AND CONVERT(date, GETDATE()) AND LEN( [UserID]) > 1 I guess […]

SQL Server : trigger deadlock

There is an InventoryCategory table and I have the following trigger attached to that table. The main purpose of trigger is to update the ProcessQueue table with InventoryCategory RowID when someone update or insert or delete a record in InventoryCategory table so that I know which records are updated recently and then I need to […]

T-SQL Nested Statement for Filtered Value / Non-Filtered Value

Trying to figure out how i might do something like this. I have to do some math similar to the following: Apples – 5 Oranges – 4 Pears – 6 Apples / Total Fruit Problem is I had to do a nested SELECT statement to filter out the specific category. I’m not sure how to […]

SQL 2008 SP1 XML Value from ntext Column

I have a column that contains the following xml: <obj xmlns:xsd=”” xmlns:xsi=”” xmlns=”urn:vim25″ versionId=”5.1″ xsi:type=”ArrayOfKeyAnyValue”> <KeyAnyValue xsi:type=”KeyAnyValue”> <key>1</key> <value xsi:type=”xsd:string”>naa.6019cbc1a09e175d370df5320b00803a</value> </KeyAnyValue> <KeyAnyValue xsi:type=”KeyAnyValue”> <key>2</key> <value xsi:type=”xsd:string”>6215</value> </KeyAnyValue> <KeyAnyValue xsi:type=”KeyAnyValue”> <key>3</key> <value xsi:type=”xsd:string”>1291898</value> </KeyAnyValue> </obj> I wish to return each of the key values as a separate column. I have tried: SELECT [table].[column1] ,CONVERT(xml, [table].[column1]).value(‘(/obj/KeyAnyValue[1]/value)[1]’, ‘nvarchar(max)’) […]

Remove unwanted space at the right of the reference field

I’m using SQL Server 2005 and try to put “” between each <field> , (+'”‘) + isnull(convert(VARCHAR(40), GUEST_CHECK_LINE_ITEM_HIST.referenceInfo, 120), ”) (+ ‘”‘) AS [“IdentityNumber”] Data Type referenceInfo nvarchar(40) Result The output data “10012 ” has a space at the right of the field. How do I remove it?

SQL Server 2012 Replication

Please help me out with a problem I am facing. I have a table T. I altered the table to alter the column C from varchar(10) to varchar(100). Now, there is a view V that is schema bound and selects columns from T. So before altering T, I had to drop the view V. Steps […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.