MSSQL XML Query EXPLICIT
FOR XML EXPLICIT 를 활용하여 데이터를 특정 XML 형태로 커스트 마이징 할수 있다.
1. 예제 테이블 생성
/****** 개체: Table [dbo].[TB_Absent_Attendance] 스크립트 날짜: 04/09/2009 14:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_Absent_Attendance](
[ab_set_dt] [char](10) COLLATE Korean_Wansung_CI_AS NOT NULL,
[att_time] [char](6) COLLATE Korean_Wansung_CI_AS NOT NULL,
[card_no] [char](12) COLLATE Korean_Wansung_CI_AS NOT NULL,
[site] [varchar](20) COLLATE Korean_Wansung_CI_AS NOT NULL,
[ab_id] [char](12) COLLATE Korean_Wansung_CI_AS NULL,
[pc_no] [varchar](3) COLLATE Korean_Wansung_CI_AS NULL,
CONSTRAINT [PK_TB_Absent_Attendance] PRIMARY KEY CLUSTERED
(
[ab_set_dt] ASC,
[att_time] ASC,
[card_no] ASC,
[site] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
2. 데이터 입력
insert into TB_Absent_Attendance Values('2009-04-01', '141120','1111', 'wowpass','','01')
insert into TB_Absent_Attendance Values('2009-04-01', '151320','2222', 'wowpass','','01')
insert into TB_Absent_Attendance Values('2009-04-01', '161120','3333', 'wowpass','','01')
3. 쿼리
Select
1 AS Tag,
0 AS Parent,
67 AS [Ubion!1!cspNo],
NULL AS [Absent!2!id],
NULL AS [Attendance!3!site],
NULL AS [Attendance!3!pc_no],
NULL AS [Attendance!3!card_no!cdata],
NULL AS [Attendance!3!ab_set_dt!ELEMENT],
NULL AS [Attendance!3!att_time!ELEMENT]
UNION ALL
SELECT
2 AS Tag,
1 As Parent,
NULL, --// Tag 1 에서 지정됨(Ubion Root)
NULL, --// Absent에서 지정됨 Tag 2
NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT top 5
3 AS Tag,
2 As Parent,
NULL, --// Absent에서 지정됨 Tag 2
NULL,
site, pc_no, card_no, ab_set_dt, att_time
FROM TB_Absent_Attendance
FOR XML EXPLICIT
