Difference between TWO dates

---------------------------------Difference between TWO dates--------------------

DECLARE @date dateTIME, @tmpdate datetime, @years int, @months int, @days int
SET @date = '06-20-1989'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

ANS: 24 1 27

SQL to XML & XML to SQL

--------convert sql to xml------------------

SET @XML=(SELECT * FROM t1 FOR XML AUTO,ELEMENTS,ROOT('MAIN'))

SELECT @XML

------------------CONVERT XML to SQL TABLE---------------

ALTER PROC usp_xml(@str XML)
AS
BEGIN
SELECT X.Y.value('id[1]','int')AS ID,X.Y.value('name[1]','varchar(50)')AS NAME FROM @str.nodes('/MAIN/t1') X(Y)
END
DECLARE @EXML XML='<MAIN>
  <t1>
    <id>1</id>
    <name>suresh</name>
  </t1>
  <t1>
    <id>2</id>
    <name>naresh</name>
  </t1>
  <t1>
    <id>4</id>
    <name>naresh</name>
  </t1>
 
</MAIN>'
EXEC usp_xml @EXML

**Note: t1-table name
             id-column id
            name-column name