Reading XML into SQL Server Table -- Simple Example

Here is the simple example to read the XML data and return it as table. Use of nodes( ) and value( ) method is done.

DECLARE @xml XML
SET @xml = '
   
         
                           
                                                      
          
         
            '
SELECT T.c.value('./@id','int') as Id
     , T.c.value('./@Title','nvarchar(50)') as Title
FROM @xml.nodes('/ROOT/documents/document') T(c)

Reset or reseed seed value of Identity column in SQL Server

DBCC CHECKIDENT is used to reseed value of Identity column.

1.   Create a table and insert few values.

CREATE TABLE Test_Identity
(
   Id INT IDENTITY(1,1),
   value INT
)
GO
INSERT INTO Test_Identity(value) VALUES(123),(456),(789)

2.    See the result in table

SELECT * FROM Test_Identity

As you see three rows are inserted. Now, lets delete this rows. Note that deleting rows from table does not reset identity value.

DELETE Test_Identity

3.     Now, lets reseed the seed value

DBCC CHECKIDENT("Test_Identity",RESEED,0)

First parameter is the table name, Second parameter is RESEED since we want to reset the seed value, and third parameter is the new seed value. Since value is 0, new row inserted in the table will have value of 1.

Lets perform an insert and check it.

INSERT INTO Test_Identity(value) VALUES(123),(456),(789)
GO
SELECT * FROM Test_Identity

As you can see the seed value is reset.

       

Clean up alphanumeric strings by splitting digits and letters into separate columns using T-SQL

This is one of the challenges taken from BeyondRelational.

Below is the problem statement.

The challenge is to read an alphanumeric string and create two output columns one containing all the digits found in the input string and the other with all the letters of the English Alphabet.

Sample Input Data

Id	AlphanumericData
1	I would like to invite my readers to participate in a TSQL Challenge.
2	You will receive 12 records
3	1234567

Expected Output

Id	AlphanumericData	Numbers	Alphabets
1	I would like to invite my readers to participate in a TSQL Challenge.	No Number Found	IwouldliketoinvitemyreaderstoparticipateinaTSQLChallenge
2	You will receive 12 records	12	Youwillreceiverecords
3	1234567	1234567	No Alphabets Found

Script to generate the data

DECLARE @tblAlphanumeric TABLE(Id INT IDENTITY,AlphaNumericData VARCHAR(1000))

INSERT INTO @tblAlphanumeric (AlphaNumericData)

SELECT 'I would like to invite my readers to participate in a TSQL Challenge.'

UNION ALL

SELECT 'You will receive 12 records'

UNION ALL

SELECT '1234567'

SELECT * FROM @tblAlphanumeric

Also, below are the other Restrictions or notes that should be considered.

The program should run in SQL SERVER 2005+.

Column names should respect the desired output shown

Output must be sorted in Ascending order of Id

Ignore those records where there is no data

If the alphanumeric data does not contain any numeric record, then the Number field should display "No Number Found".

If the alphanumeric data does not contain any alphabetic record, then the Alphabets field should display "No Alphabets Found".

The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.

You cannot use RBAR, cursors, loops etc. in your program. 

Solution

;WITH DATA

AS

(

 SELECT Id

   , AlphaNumericData

   , CASE WHEN AlphaNumericData IS NULL

          OR ISNULL(AlphaNumericData,'') = '' THEN 0

        ELSE CHARINDEX(' ',AlphaNumericData,0)

        END AS char_index

   , CASE WHEN AlphaNumericData IS NULL

            OR ISNULL(AlphaNumericData,'') = '' THEN NULL

          WHEN CHARINDEX(' ',AlphaNumericData,0) <> 0

            THEN SUBSTRING(AlphaNumericData,CHARINDEX(' ',AlphaNumericData,0) +1 , LEN(AlphaNumericData))

          END AS truncatedData             

   , CONVERT(VARCHAR(max),

          CASE WHEN CHARINDEX(' ',AlphaNumericData,0) = 0

                AND ISNULL(AlphaNumericData,'') <> ''

                AND ISNUMERIC(AlphaNumericData) = 1

                  THEN AlphaNumericData

                WHEN ISNUMERIC(SUBSTRING(AlphaNumericData,0,CHARINDEX(' ',AlphaNumericData,0))) = 1

                  THEN SUBSTRING(AlphaNumericData,0,CHARINDEX(' ',AlphaNumericData,0))

               ELSE ''   

               END

             )AS Numeric

   , CONVERT(VARCHAR(max),             

          CASE WHEN CHARINDEX(' ',AlphaNumericData,0) = 0

                AND ISNULL(AlphaNumericData,'') <> ''

                AND ISNUMERIC(AlphaNumericData) <> 1

                THEN AlphaNumericData

               WHEN ISNUMERIC(SUBSTRING(AlphaNumericData,0,CHARINDEX(' ',AlphaNumericData,0))) <> 1

                THEN SUBSTRING(AlphaNumericData,0,CHARINDEX(' ',AlphaNumericData,0))

               ELSE ''   

                                                                           END

             )AS Alphabets

  FROM @tblAlphanumeric

    

UNION ALL

SELECT d.Id

   , d.AlphaNumericData

   , CASE WHEN d.truncatedData IS NULL

                                                  OR ISNULL(truncatedData,'') = '' THEN 0

        ELSE CHARINDEX(' ',truncatedData,0)

                                                END AS char_index

   , CASE WHEN d.truncatedData IS NULL

          OR ISNULL(d.truncatedData,'') = '' THEN NULL

        WHEN CHARINDEX(' ',d.truncatedData,0) <> 0

          THEN SUBSTRING(d.truncatedData,CHARINDEX(' ',d.truncatedData,0) +1 , LEN(d.truncatedData))

                                                END AS truncatedData 

   , CONVERT(VARCHAR(max),d.Numeric+

                                                                                                (CASE WHEN CHARINDEX(' ',d.truncatedData,0) = 0

                                                                                                       AND ISNULL(d.truncatedData,'') <> ''

                                                                                                       AND ISNUMERIC(d.truncatedData) = 1

                       THEN d.truncatedData

                                                                                                                          WHEN ISNUMERIC(SUBSTRING(d.truncatedData,0,CHARINDEX(' ',d.truncatedData,0))) = 1

                       THEN SUBSTRING(d.truncatedData,0,CHARINDEX(' ',d.truncatedData,0))

                                                                                                      ELSE ''   

                                                                                                                          END)) AS Numeric 

   , CONVERT(VARCHAR(max),d.Alphabets+

                                                                        (CASE WHEN CHARINDEX(' ',d.truncatedData,0) = 0

                                                                                                   AND ISNULL(d.truncatedData,'') <> ''

                                                                                                   AND ISNUMERIC(d.truncatedData) <> 1

                   THEN d.truncatedData

                  WHEN ISNUMERIC(SUBSTRING(d.truncatedData,0,CHARINDEX(' ',d.truncatedData,0))) <> 1

                   THEN SUBSTRING(d.truncatedData,0,CHARINDEX(' ',d.truncatedData,0))

                                                                                                  ELSE ''   

                                                                                                  END)) AS Alphabets          

FROM DATA d

WHERE d.truncatedData IS NOT NULL

    

)

select d.Id, d.AlphaNumericData,

     CASE WHEN Numeric = '' THEN 'No Number Found'

              ELSE Numeric

        END AS Numbers,

     CASE WHEN Alphabets = '' THEN 'No Alphabets Found'

              ELSE Alphabets

        END AS Alphabets

from DATA d

where truncatedData is null

order by d.Id

You can find other solutions for the same problem at BeyondRelational

Find the Parents and Adjacent Members of the Node using T-SQL

This is one of the challenges taken from BeyondRelational.

Below is the problem statement.

The challenge is to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

Sample Input Data

ParentId ChildId    Name

-------- ------- --------

NULL 1 Niladri Biswas

1 2 Piyush Ghosh

1 3 Agnish Basu

1 4 Deepak Kumar Goyal

2 5 Sachin Srivastav

2 6 Nishant Mandilwar

5 7 Arindam Pal

5 8 Mahi Sharma

3 9 Mahima Roy

3 10 Simran Motilal

9 11 Raj Malhotra

9 12 Sharmistha Roy

10 13 Preeti Sen

10 14 Holly Huggins

Given a @ChildId = 6 as Parameter should give below output

Expected Output

Id Name

--- ----

1 Niladri Biswas

3    Agnish Basu

4    Deepak Kumar Goyal

2    Piyush Ghosh

6        Nishant Mandilwar

5        Sachin Srivastav

Script to generate the data

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );

INSERT INTO @ParentChild 

SELECT null, 1, 'Niladri Biswas' UNION ALL 

SELECT 1, 2 ,'Piyush Ghosh' UNION ALL  

SELECT 1,3 ,'Agnish Basu' UNION ALL  

SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL

SELECT 2,5, 'Sachin Srivastav' UNION ALL 

SELECT 2,6, 'Nishant Mandilwar' UNION ALL 

SELECT 5,7 ,'Arindam Pal' UNION ALL 

SELECT 5,8,'Mahi Sharma' UNION ALL 

SELECT 3,9, 'Mahima Roy' UNION ALL 

SELECT 3,10, 'Simran Motilal' UNION ALL 

SELECT 9,11,'Raj Malhotra' UNION ALL

SELECT 9,12,'Sharmistha Roy' UNION ALL 

SELECT 10,13,'Preeti Sen'  UNION ALL 

SELECT 10,14,'Holly Huggins'

Also, below are the other Restrictions or notes that should be considered.

The solution should work on SQL Server 2005 and above.

Column names should respect the desired output shown

Output must be sorted in DESCENDING ORDER of Name

The output should show the hierarchical relationship between the employees (as shown in the example above)

The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.

You cannot use RBAR, cursors, loops etc. in your program. 

Solution

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );

INSERT INTO @ParentChild

      SELECT null, 1, 'Niladri Biswas' UNION ALL

      SELECT 1, 2 ,'Piyush Ghosh' UNION ALL 

      SELECT 1,3 ,'Agnish Basu' UNION ALL 

      SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL

      SELECT 2,5, 'Sachin Srivastav' UNION ALL

      SELECT 2,6, 'Nishant Mandilwar' UNION ALL

      SELECT 5,7 ,'Arindam Pal' UNION ALL

      SELECT 5,8,'Mahi Sharma' UNION ALL

      SELECT 3,9, 'Mahima Roy' UNION ALL

      SELECT 3,10, 'Simran Motilal' UNION ALL

      SELECT 9,11,'Raj Malhotra' UNION ALL

      SELECT 9,12,'Sharmistha Roy' UNION ALL

      SELECT 10,13,'Preeti Sen'  UNION ALL

      SELECT 10,14,'Holly Huggins'

DECLARE @ChildId INT

SELECT @ChildId = 6    

     

;WITH WHOLE_TREE

AS

(

      SELECT Childid, Parentid, Name, 0 as level

      FROM @ParentChild

      where Parentid is null

     

      UNION ALL

     

      SELECT pc.Childid, pc.Parentid, pc.Name, (d.level + 1) as level

      FROM WHOLE_TREE d

            INNER JOIN @ParentChild pc

                  ON d.Childid = pc.Parentid

),

SUBTREE

AS

(

      SELECT w.Childid, w.Parentid, w.Name, w.level FROM WHOLE_TREE w

      WHERE          level < (SELECT level FROM WHOLE_TREE WHERE Childid = @ChildId)

     

      UNION

     

      SELECT w.Childid, w.Parentid, w.Name, w.level FROM WHOLE_TREE w

      WHERE          level = (SELECT level FROM WHOLE_TREE w2 WHERE Childid = @ChildId)

              AND w.parentId = (SELECT ParentId FROM WHOLE_TREE w3 WHERE ChildId = @ChildId)                             

),

FINAL_OUTPUT

AS

(

      SELECT Childid, CONVERT(VARCHAR,Name) AS Name, level, 0 as processedLevel

      FROM SUBTREE

     

      UNION ALL

     

      SELECT f.Childid, CONVERT(VARCHAR,'     '+f.Name) as Name, f.level, (f.processedLevel + 1) as processedLevel

      FROM FINAL_OUTPUT f

            INNER JOIN SUBTREE s

                  on (f.processedLevel + 1) = S.level

      WHERE (f.processedLevel + 1) <= f.level              

),

DISTINCT_SET

AS

(

      SELECT DISTINCT * FROM FINAL_OUTPUT WHERE level = processedLevel

),

FINAL

AS

(

      SELECT Childid, Name, ROW_NUMBER()OVER(ORDER BY level,Name) as rank FROM DISTINCT_SET

      WHERE level = processedLevel

)

SELECT Childid as Id, Name FROM FINAL

You can find other solutions for the same problem at BeyondRelational