Nov 19, 2008

XML output in sqlserver 2005

SELECT 1 AS TAG,
NULL AS PARENT,
NULL AS [ROOT!1],
NULL AS [TABLE!2],
NULL AS [FIELDS!3!ertr],
NULL AS [FIELDS!3!rtr]
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL,
'TEMP',
NULL,
NULL
UNION ALL
SELECT 3 AS TAG,
2 AS PARENT,
NULL,
NULL,
RTRIM(ERTR),
RTRIM(RTR)
from temp
FOR XML EXPLICIT

craeteing temp table in sqlserver 2005

CREATE TABLE #names_sample (
name_id INT IDENTITY(1, 1) NOT NULL,
company_id varchar(50),
full_name VARCHAR(110) NULL,
first_name VARCHAR(50) NULL,
middle_name VARCHAR(50) NULL,
last_name VARCHAR(30) NULL,
suffix VARCHAR(20) NULL
)

sql case statement with selecting records.

select case
when len(id)=5 then 'CO'+'000'+cast(id as varchar(5))
when len(id)=6 then 'CO'+'00'+cast(id as varchar(6))
when len(id)=7 then 'CO'+'0'+cast(id as varchar(7))
end,ContactPosition,CompanyID
from contact_id_temp