안녕하세요. SQL 출력결과를 XML 형태로 출력하고 싶습니다.
만약 아래와 같은 2개의 테이블에 데이터가 들어가 있다고 가정 한다면
SELECT BOOK_TITLE
,BOOK_WRITER
,publishing_company
FROM BOOK_MST
WHERE BOOK_ID = 100;
BOOK_MST 라는 테이블에 아래와 같이 DATA가 있고
-------------------------------
BOOK_TITLE | BOOK_WRITER | publishing_company
어린왕자 조인성 XX출판사
--------------- -----------
SELECT borrow_date
,borrow_name
FROM BOOK_HISTORY
WHERE BOOK_ID = 100;
BOOK_HISTORY 라는 테이블에 아래와 같이 DATA가 있다고 가정하고
-----------------------------------
borrow_date | borrow_name
2018.04.10 홍길동
2018.02.04 이길동
2017.05.25 고두리
-------------------
순환 SQL를 XML 형태로 만들고 싶습니다.
< ROOT>
< HEADER>
< TITLE>어린왕자< /TITLE>
< WRITER>조인성< /WRITER>
< publishing_company>XX출판사< /publishing_company>
< /HEADER>
< BORROW>
< RECORD>
< borrow_date>2018.04.10< /borrow_date>
< borrow_name>홍길동< /borrow_name>
< /RECORD>
< RECORD>
< borrow_date>2018.02.04< /borrow_date>
< borrow_name>이길동< /borrow_name>
< /RECORD>
< RECORD>
< borrow_date>2017.05.25< /borrow_date>
< borrow_name>고두리< /borrow_name>
< /RECORD>
< /BORROW>
< /ROOT >
SELECT a.book_id , CONCAT( '<ROOT>' , CHAR(13), CHAR(10), ' <HEADER>' , CHAR(13), CHAR(10), ' <TITLE>' , a.book_title , '</TITLE>' , CHAR(13), CHAR(10), ' <WRITER>' , a.book_writer , '</WRITER>' , CHAR(13), CHAR(10), ' <publishing_company>', a.publishing_company, '</publishing_company>' , CHAR(13), CHAR(10), ' </HEADER>' , CHAR(13), CHAR(10), ' <BORROW>' , GROUP_CONCAT(CONCAT( CHAR(13), CHAR(10), ' <RECORD>' , CHAR(13), CHAR(10), ' <borrow_date>', b.borrow_date, '</borrow_date>' , CHAR(13), CHAR(10), ' <borrow_name>', b.borrow_name, '</borrow_name>' , CHAR(13), CHAR(10), ' </RECORD>' ) ORDER BY b.borrow_date DESC) , CHAR(13), CHAR(10), ' </BORROW>' , CHAR(13), CHAR(10), '</ROOT>' ) x FROM (-- book_mst -- SELECT 100 book_id, '어린왕자' book_title, '조인성' book_writer, 'XX출판사' publishing_company ) a INNER JOIN (-- book_history -- SELECT 100 book_id, '2018.04.10' borrow_date, '홍길동' borrow_name UNION ALL SELECT 100, '2018.02.04', '이길동' UNION ALL SELECT 100, '2017.05.25', '고두리' ) b ON a.book_id = b.book_id WHERE a.book_id = 100 GROUP BY a.book_id, a.book_title, a.book_writer, a.publishing_company ;