안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 2-13. SELECT 결과셋을 XML, JSON 형식으로 출력을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번 강좌에서 진행할 내용은 SELECT 결과셋을 XML, JSON 형식으로 출력입니다.
TL;DR
SQL Server에서 SELECT 결과를 JSON이나 XML로 출력 가능하고, 생성한 출력 결과를 애플리케이션에 전달하거나, 다른 데이터 플랫폼으로 전송 가능합니다.
SQL Server에서 SELECT 실행 결과를 JSON이나 XML 포맷으로 애플리케이션에 전달하거나 다른 데이터 플랫폼으로 복사해야 할 경우가 있습니다. 이때, SQL 구문으로 JSON이나 XML 형식 데이터를 생성할 수 있어 유용합니다.
이번 예제에서도 이전 JOIN 강좌에서 생성한 products 테이블을 이용합니다.
XML 포맷 결과 생성
FOR XML AUTO 실행
USE AdventureWorks; GO -- XML 포맷으로 결과 출력 SELECT product_name, price FROM products FOR XML AUTO; GO
결과 <products product_name="초코파이" price="1200" /> <products product_name="칸쵸" price="800" /> <products product_name="포테토칩" price="1800" /> <products product_name="꼬북칩" price="1300" />
이렇게 XML AUTO 기본 출력은 XML 속성(attribute) 형태로 출력됩니다.
XML 구조를 기술한 스키마를 결과에 포함
XML 스키마는 XML 데이터의 구조 정보를 포함할 수 있습니다. 이렇게 스키마와 데이터를 생성할 수 있습니다.
-- XML 스키마 포함 SELECT product_name, price FROM products FOR XML AUTO, XMLSCHEMA; GO
결과 <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet3" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet3" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified"> <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" /> <xsd:element name="products"> <xsd:complexType> <xsd:attribute name="product_name"> <xsd:simpleType> <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52"> <xsd:maxLength value="10" /> </xsd:restriction> </xsd:simpleType> </xsd:attribute> <xsd:attribute name="price"> <xsd:simpleType> <xsd:restriction base="sqltypes:decimal"> <xsd:totalDigits value="10" /> <xsd:fractionDigits value="0" /> </xsd:restriction> </xsd:simpleType> </xsd:attribute> </xsd:complexType> </xsd:element> </xsd:schema> <products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" product_name="초코파이" price="1200" /> <products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" product_name="칸쵸" price="800" /> <products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" product_name="포테토칩" price="1800" /> <products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet3" product_name="꼬북칩" price="1300" />
스키마와 데이터가 포함된 문서가 출력됩니다.
☑️ 챗GPT 활용: XML 스키마에 대해서 설명해 줘
XML ELEMENT(요소) 형식 출력
속성(Attribute) 중심 결과를 요소(Element) 중심 결과로 출력할 수 있습니다.
-- XML ELEMENT(요소) 형식 출력 SELECT product_name, price FROM products FOR XML AUTO, ELEMENTS XSINIL; GO
결과 <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <product_name>초코파이</product_name> <price>1200</price> </products> <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <product_name>칸쵸</product_name> <price>800</price> </products> <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <product_name>포테토칩</product_name> <price>1800</price> </products> <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <product_name>꼬북칩</product_name> <price>1300</price> </products>
요소 포맷으로 XML 문서를 출력합니다. SQL Server T-SQL 확장 기능으로 이렇게 손쉬운 XML 출력이 가능합니다.
☑️ 챗GPT 활용: XML 속성(Attribute) 중심 결과와 요소(Element) 중심 결과에 대해서 설명해 줘
JSON 출력
FOR JSON 구문을 이용하면 SELECT 결과셋을 JSON 포맷으로 출력합니다.
--JSON 포맷 출력 SELECT product_name, price FROM products FOR JSON AUTO; GO
결과 [ { "product_name": "초코파이", "price": 1200 }, { "product_name": "칸쵸", "price": 800 }, { "product_name": "포테토칩", "price": 1800 }, { "product_name": "꼬북칩", "price": 1300 } ]
이렇게 JSON 형식으로 떨어집니다.
두 개 테이블의 JOIN 결과를 JSON으로 출력하면 이렇게 중첩 형태로 출력됩니다.
-- 2개 테이블 JOIN 결과를 JSON으로 출력 SELECT products.product_name, products.price, orders.order_date, orders.total_amount FROM products INNER JOIN orders ON products.product_id = orders.product_id FOR JSON AUTO; GO
결과 [ { "product_name": "초코파이", "price": 1200, "orders": [ { "order_date": "2022-01-01", "total_amount": 101 } ] }, { "product_name": "포테토칩", "price": 1800, "orders": [ { "order_date": "2022-02-05", "total_amount": 75 } ] }, { "product_name": "칸쵸", "price": 800, "orders": [ { "order_date": "2022-03-10", "total_amount": 151 }, { "order_date": "2022-04-15", "total_amount": 200 } ] } ]
JSON으로 중첩 구조가 출력된 것을 확인할 수 있습니다.
SQL 쿼리 구문을 FOR XML과 FOR JSON 구문으로 형식에 맞춰 출력할 수 있으며, 클라이언트 애플리케이션에서 결과를 바로 사용할 수 있습니다.
뿐만 아니라, SQL Server와 같은 관계형 데이터베이스에서 JSON과 XML을 더 다양하게 활용할 수 있습니다. SELECT-FOR 구문으로 단순히 출력하는 정도가 아니라, SQL 구문으로 JSON 데이터를 변환, 컬럼 데이터와 조합하거나 JSON 데이터를 OPENJSON 등으로 SQL 테이블형 결과셋으로 생성하고 다양한 작업을 하는 것도 가능합니다.
DECLARE @json NVARCHAR(MAX) SET @json = N' [ {"product_name":"초코파이","price":1200}, {"product_name":"칸쵸","price":800}, {"product_name":"포테토칩","price":1800}, {"product_name":"꼬북칩","price":1300} ]' SELECT * FROM OPENJSON(@json) WITH ( product_name NVARCHAR(10) 'strict $.product_name', price INT 'strict $.price'); GO 결과 product_name price ------------ ----------- 초코파이 1200 칸쵸 800 포테토칩 1800 꼬북칩 1300 (4개 행이 영향을 받음)
☑️ 챗GPT 활용: SQL Server OPENJSON에 대해서 설명해 줘
이렇게 JSON을 테이블형 결과셋으로 변환할 수도 있습니다.
이후 SQL 프로그래밍 강좌에서 더 다양한 예제를 다루게 되니 도움 되시길 바랍니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.