Recursive Common Table Expression

DECLARE @Date DATE = '1900-01-01',
@inc INT = 0

;with cte as
(
select @inc AS Inc,DATENAME(mm,@Date) AS [MonthName],DATEPART(mm,@Date) AS [MonthNumber]
UNION ALL
select inc+1,DATENAME(mm,DATEADD(mm,inc+1,@Date)),DATEPART(mm,DATEADD(mm,inc+1,@Date))
FROM cte
where inc < 11
)


select [MonthName],[MonthNumber] from cte

XML File to SQL Table

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(max)

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'D:\MyShopData\MobileOperatorIdentify.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML

SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
= @x
END

SELECT @FileContents as FileContents
DROP TABLE #tempXML
print @FileContents
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc OUTPUT, @FileContents

SELECT PrefixId, OperatorId, PrefixNumber
FROM OPENXML(@iDoc,'/DocumentElement/dtMobileOperatorPrefix',2)
With(
PrefixId varchar(10) 'PrefixId',
OperatorId varchar(10) 'OperatorId',
PrefixNumber varchar(10) 'PrefixNumber'
)

xml in a tabular format in sqlserver 2008

declare @xml xml
set @xml ='
--
-- abc@gmail.com
--
--
--
--
--
--
--def@gmail.com
--
--
--
--
--
--'
/*-----------------------------------------------------
abc@gmail.com abc 123
abc@gmail.com def 345
def@gmail.com abc 123
def@gmail.com def 345
*/


SELECT
x.v.value('../../EA[1]','varchar(100)') as EMail,
x.v.value('@Name[1]','varchar(1000)') as CAS,
x.v.value('@value[1]','varchar(1000)') as Value

FROM @xml.nodes('CampaignItems/Record/CAS/Attr') x(v)