XML column compare in SQl server 2005

I want to compare two XML columns with multiple rows in SQL Server 2005.

Table structure is as below

CREATE TABLE [dbo].[UpdationLog](
    [LogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CustID] [int] NOT NULL,
    [OldValue] [xml] NOT NULL,
    [NewValue] [xml] NOT NULL,
 CONSTRAINT [PK_UpdationLog] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[UpdationLog] VALUES
(1526,'<ccm CustID="1526" CustName="Teja" Address="Bangalore"/>','<ccm CustID="1526" CustName="Tejas" Address="Bengaluru"/>'),
(1245,'<ccm CustID="1245" CustName="Abhi" Address="Andhra"/>','<ccm CustID="1245" CustName="Abhilash" Address="Andra Pradesh"/>'),
(1145,'<ccm CustID="1145" CustName="Abhi" Address="Assam"/>','<ccm CustID="1145" CustName="Abhinandan" Address="Assam"/>')

I want to compare XML column OldValue and NewValue and display updated records.

Desired Output

|-------|-------------|---------------|------------
|CustID |   Attribute |     OldValue  | NewValue
|-------|-------------|---------------|---------
|1526   | CustName    |  Teja         | Tejas
|1526   | Address     | Bangalore     | Bengaluru
|1245   | CustName    |  Abhi         | Abhilash
|1245   | Address     |  Andhra       | Andra Pradesh
|1145   | CustName    |  Abhi         | Abhinandan

http://ift.tt/2e3Glvw

VB.net how to create table using xml and displaying the table on the webpage

Can somebody please help me to amend this code because i tried running but the table didnt appear on the page. I'm new to XML and somebody gave me this snippet saying that XML coding is more reliable for tables.

The code probably works but i dont know how to display it to the page. I tried searching but i saw that many also did nearly the same thing but why didnt it work for me? While, some others made another xml file to keep the data, but in my case, i'm actually planning to retrieve data from a MS SQL 2005 database and display it in a table. What am i missing here?

Dim dt As DataTable = Me.GetData()
    For Each row As DataRow In dt.Rows
        Dim tr As XElement = <tr></tr>

        For Each column In dt.Columns
            Dim td As XElement = <td></td>
            td.Value = row(column.Ordinal).ToString()

            tr.Add(td)
        Next

        table.Add(tr)
    Next

Need help concat multiple fields into one based off of other fields

I asked a similar question recently but, unfortunately, the nature of the request changed. Been looking at this code for a bit but my brain is friend and I can't quite think of a way to get this working as needed.

Working in SQL 2005 and this will encompass hundreds of values like this.

Thanks in advance!

Sample Table

+----+-------------+---------------------+---------------------+
| ID |    CLASS    |  PARENT_ATTRIBUTE   |      ATTRIBUTE      |
+----+-------------+---------------------+---------------------+
|  1 | Genre       | A                   | Hip Hop             |
|  1 | Genre       | B                   | Pop                 |
|  1 | Instruments | Keyboards           | Synth               |
|  1 | Instruments | Keyboards           | Grand Piano         |
|  1 | Instruments | Drums               | Kit                 |
|  1 | Moods       | Positive/Optimistic | Uplifting/Inspiring |
|  1 | Moods       | Positive/Optimistic | Happy/Feel Good     |
|  1 | Moods       | Musical Feel        | Pulsing             |
+----+-------------+---------------------+---------------------+

Desired Output

+----+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| ID | MOODS                                                                                                    | INSTRUMENTS                                             |
| 1  |Positive/Optimistic - Uplifting/Inspiring, Positive/Optimistic -  Happy/Feel Good, Musical Feel - Pulsing | Keyboards - Synth, Keyboards - Grand Piano, Drums - Kit |
+----+----------------------------------------------------------------------------------------------------------+---------------------------------------------------------+

Sum Sql column with join

I'm trying this code:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'; 

I'm getting error:

Msg 8118, Level 16, State 1, Line 1 Column 'C.CustomerNum' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Convert into column using delimited PIPE in MS SQL

Present value

All (one column)

--

43 | 00043 | 22/09/2016 | Ooredoo - AuU TX Projects | Raj singh

I want it should be in separate column like given below

ID Key Date Company Owner

43 00043 22/09/2016 Ooredoo - AuU TX Projects Raj singh

Pull up the most recent record including joining 2 tables and filters

I have seen a lot of posts on pulling up the most recent record. I haven't been able to find one that includes joining another table and filters.

What I need is information regarding the most recent document (record) created, but only if it meets certain criteria. PLUS I need to pull in some data from another table.

s504Plans Table

    Student ID   |   Firstname   |   Startdate   |   Status
    ----------       ---------       ---------       ------
    111111            Johnny         1/5/2015          F
    222222            Sue            4/7/2016          I
    333333            Barb           2/5/2016          F
    111111            Johnny         2/1/2016          F

Cases Table

    Student ID   |   School   |   
    ----------       ------
    111111           Franklin
    222222           Eisenhower
    333333           Franklin

And the results I'd like to see are only the most recent document where the status of the document is F...

    Student ID  |  Firstname  |  Startdate  |  Status  |   School
    ----------     ---------     ---------     ------      ------
    111111          Johnny       2/1/2016        F         Franklin
    333333          Barb         2/5/2016        F         Franklin

Thanks!

Showing "Invalid object name " in sqlServer?

While Executing the Following query it showing the Invalid object name '#temp1'. can any body knows the error occurred due to which reason

SELECT @sql = N'--
                    SELECT * into #temp1
                    FROM (
                            SELECT [BranchID],
                            [SubInventory],
                            [Product],
                            [Stock] 

                    FROM #MyTempTable
                    WHERE [BranchName] = ''' +@BranchName +'''
                    ) as t
                PIVOT (
                MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')

        ) as pvt'

    EXEC sp_executesql @sql

    select * from #tmepeee