View XML nodes in SQL Server as separate column

Hi, today i was asked by someone to view Xml file nodes in SQL Server as separated columns to check the consistency of data available in nodes.

I already knew that because i have done those kinds of stuff in beginning of my developer carrier.

Below Query will give you exact out but its little bit judicious because you have to give nodes name manually. I am working out to get the simple and easy solution when i will achieve my goal i will update this blog.

Demo xml data='<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

<Root source="C:\Data.csv" datetime="2015-05-12T16:16:55+06:00" >

     <record num="1">

          <ID>131383</ID>

          <EmpNum>51023</EmpNum>

          <Login>Rty</Login>

          <First_name>Amit</First_name>        

     </record>

    '

SELECT @XmlFile = BulkColumn

FROM  OPENROWSET(BULK 'path of xml', SINGLE_BLOB) x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlFile

SELECT ID, EmpNum, [Login],First_name

FROM OPENXML(@hDoc, 'Root/record')

WITH

(

ID varchar(50) '(ID)[1]',

EmpNum varchar(50) '(EmpNum)[1]',

[Login] varchar(100) '(Login)[1]',

First_name varchar(100) '(First_name)[1]'

)

EXEC sp_xml_removedocument @hDoc

GO

Search record among multiple columns of SQL table.

Today i have learnt a great thing related to searching a value among all columns of a SQL table. That is very useful even from this i can search a value in specific column of all table in SQL database.

Suppose you have a table this :

CREATE TABLE tblTest
(id INT IDENTITY(1,1),
columnA NVARCHAR(50),
coulmnB INT,
coulmnC NVARCHAR(250))-- Create a table 

INSERT INTO tblTest (columnA,coulmnB,coulmnC) VALUES
('Grim',2,'Master'),
('Kingdom',200,'Amar'),
('Paula',300,'Sara'),
('Kingdom',400,'Grim')--Inserting some Dummy Records

Now you have a requirement to search value 'Grim'  in a table for all column. In that case you will think to put where clause on each column of SQL table. but there is an alternative to do that. You can user below query to search a value in all columns of SQL table.

SELECT * FROM tblTest WHERE 'Grim' IN (columnA,coulmnB,coulmnC)

How to filter duplicate rows (duplicate records by column)

Today my old project manager ask me for a favor to give him a SQL query which exclude duplicate rows.

let me direct come to the point and show you some pictorial presentation.

--Query--
select data.provider_id, data.person_id, data.rank, data.RowNumber from
 (
 select provider_id, person_id
 , Rank()
 over (
 PARTITION BY provider_id
 ORDER BY provider_id ) AS [rank],
 ROW_NUMBER() OVER (
 PARTITION BY provider_id
 ORDER BY provider_id DESC ) as RowNumber
 from testtble

 ) as data
 where data.RowNumber <= 5
 order by provider_id

you can see that in row 2 and 3 provider and person id's are repeated.
To avoid the duplicacy i have to first find the duplicate rows based on columns records.
So i have created below query to remove that duplicacy from result.

--Query--
;WITH CTE AS(
SELECT *,(RowsNumber - Rank_Number + 1) AS Rank_Item FROM
    ( SELECT PROVIDER_ID ,
                PERSON_ID ,
                ROW_NUMBER() OVER( ORDER BY provider_id) AS RowsNumber ,
                RANK() OVER( ORDER BY provider_id) AS Rank_Number
           FROM(
        SELECT
  PROVIDER_ID ,
  PERSON_ID ,
  COUNT(*) AS CNT
                   FROM
  TESTTBLE
                   GROUP BY
  PROVIDER_ID ,
                            PERSON_ID
               )A
       )B
     )SELECT * FROM CTE WHERE Rank_Item<=5
      ORDER BY PROVIDER_ID;