MSJO.krJSON은 최신 웹 및 모바일 애플리케이션에서 데이터를 교환하는 데 사용되는 일반적인 텍스트 데이터 형식이다. 예전 JSON이 일반화되기 전에는 XML 형태로 문서를 주고받았고 지금도 XML 형식은 데이터의 검증이 필요한 곳에서 스키마를 사용하여 무결성을 검증할 뿐만 아니라 표준 ‘XML Web Services’의 기본 데이터 형식이기도 하다. 이번 글에서는 SQL Server(2019)에서 개발언어의 도움 없이 T-SQL 자체만으로 XML, JSON 형식을 다루는 몇 가지 예제를 소개한다.
일반적인 XML 리턴
select b.BoardNO,
b.UserNO,
b.Contents,
b.HitCount,
format(b.RegDate, 'yyyyMMdd') as RegDate,
b.PublicIP,
b.LocalIP,
u.UserName,
u.DeptNO,
null as nullTest
from TBoard b
join TUser u on u.UserNO = b.UserNO
for xml path('board'), root('boards'), elements xsinil
xml explicit
select 1 as tag,
null as parent,
null as 'boards!1!',
null as 'board!2!BoardNO',
null as 'board!2!UserNO',
null as 'board!2!Contents',
null as 'board!2!HitCount',
null as 'board!2!RegDate',
null as 'board!2!PublicIP',
null as 'board!2!LocalIP',
null as 'board!2!UserName',
null as 'board!2!DeptNO'
union all
select 2 as tag,
1 as parent,
null,
b.BoardNO,
b.UserNO,
b.Contents,
b.HitCount,
format(b.RegDate, 'yyyyMMdd') as RegDate,
b.PublicIP,
b.LocalIP,
u.UserName,
u.DeptNO
from TBoard b
join TUser u on u.UserNO = b.UserNO
for xml explicit
xml cdata
select 1 as tag,
null as parent,
b.BoardNO as 'board!1!BoardNO!element',
b.UserNO as 'board!1!UserNO!element',
b.Contents as 'board!1!Contents!cdata', -- cdata or xml
b.HitCount as 'board!1!HitCount!element',
format(b.RegDate, 'yyyyMMdd') as 'board!1!RegDate!element',
b.PublicIP as 'board!1!PublicIP!element',
b.LocalIP as 'board!1!LocalIP!element',
u.UserName as 'board!1!UserName!element',
u.DeptNO as 'board!1!DeptNO!element'
from TBoard b
join TUser u on u.UserNO = b.UserNO
for xml explicit, root('boards')
xml to table
declare @xml xml
set @xml =
'<?xml version="1.0" encoding="utf-8"?>
<boards>
<board>
<userid>123</userid>
<username>abc</username>
</board>
</boards>'
select n.value('(./userid/text())[1]','int') as userid,
n.value('(./username/text())[1]','varchar(50)') as username
from @xml.nodes('/boards/board') as a(n)
일반적인 JSON 리턴
select b.BoardNO,
b.UserNO,
b.Contents,
b.HitCount,
format(b.RegDate, 'yyyyMMdd') as RegDate,
b.PublicIP,
b.LocalIP,
[User].UserName,
[User].DeptNO
from TBoard b
join TUser [User] on [User].UserNO = b.UserNO
for json auto, include_null_values
json path
-- [example 1]
declare @result nvarchar(max)
set @result = (
select b.BoardNO,
b.UserNO,
b.Contents,
b.HitCount,
format(b.RegDate, 'yyyyMMdd') as RegDate,
b.PublicIP,
b.LocalIP,
u.UserName as 'user.UserName',
u.DeptNO as 'user.DepatNO'
from TBoard b
join TUser u on u.UserNO = b.UserNO
for json path, root('boards'), include_null_values
)
select @result as result
-- [example 2]
SELECT
ent.Id AS 'Id',
ent.Name AS 'Name',
ent.Age AS 'Age',
EMails = (
SELECT
Emails.Id AS 'Id',
Emails.Email AS 'Email'
FROM EntitiesEmails Emails WHERE Emails.EntityId = ent.Id
FOR JSON PATH
)
FROM Entities ent
FOR JSON PATH
json to table
-- [example 1]
declare @json nvarchar(max)
set @json='{"userid":"A123","username":"가나닭","age":45,"skills":["sql","c#","mvc"]}';
select * from openjson(@json)
with(
userid varchar(100) '$.userid' ,
username varchar(100) '$.username',
age int '$.age',
skills nvarchar(max) as json)
-- [example 2]
declare @json nvarchar(max)
set @json='{"userid":"A123","username":"가나닭","age":45,"skills":["sql","c#","mvc"]}';
select userid, username, age, s.value from openjson(@json)
with(
userid varchar(100) '$.userid' ,
username varchar(100) '$.username',
age int '$.age')
cross apply openjson(@json, '$.skills') as s
-- [example 3]
declare @json nvarchar(max)
set @json='{"userid":"A123","username":"가나닭","age":45,"items":[{"A":"A", "B":"B"},{"A":"C", "B":"D"}]}';
select * from openjson(@json)
with(
userid varchar(100) '$.userid' ,
username varchar(100) '$.username',
age int '$.age',
items nvarchar(max) as json)
cross apply openjson(items)
with (A varchar(100), B varchar(100))