XML SQL Server 2000 XML Web Time Feelanet

XML과 SQL Server 2000에서 지원하는 XML 처리 방법 정 홍주 Web. Time Feelanet

목차 v v . NET Framework SQLXML 과 관련된 XML기술 SQL Server 2000에서의 XML 지원

. NET Framework 브라우저 데이터 저장소 ADO. NET HTML, XML SOAP, XMLHTTP . NET RMI Handler /ASP. NET / SOAP World! Web Server 보안 트랜잭션 Serviced Component Managed Object 동시성 CLR 컨텍스트 OS COM+ Server ADO. NET / XML Schema

. NET vs. SQLXML v. NET XML-Object Mapping Layer n XML Serialization n XML-Relation Mapping Layer n ADO. NET v SQLXML n XML-Relation Mapping Layer n

XML vs Relational v Relational format : data storage n 각 entity 의 instance는 row n 각 entity의 property는 column n Relationships은 key field를 기반으로 v XML : data exchange n 각 entity의 instance는 element n 각 entity의 Properties는 value, attribute, 또는 child element n Relationships은 계층적으로 묘사

E-Commerce 시나리오 Supplier Extranet Purchase order Intranet Delivery request Catalog Retailer Web site Customer Shipper

SQLXML RDB의 구조적 데이터를 XML로 변환 v XML을 RDB의 구조적 데이터로 변환 v XML-Relation Mapping Layer v <XML> Retailer XML business document, e. g. , purchase order Supplier

SQLXML Architecture Client COM Middle Tier IIS ISAPI Updategram SQLOLE DB Translation of: n Query request n n HTT P ADO Updategrams Query templates XPath queries Annotat ed Schema s SQL Server SQL executed: n n n Open XML T-SQL Update Insert Delete Select…For XML

SQLXML Architecture

SQLXML 과 관련된 XML기술 v XML Schema v XPath v XSLT

XML Schema v Schema는 XML 문서의 규약을 동의 n element, attribute –데이터타입, 순서, 길이, 최대/최소값 Schema <XML> Retailer XML business document, e. g. , purchase order Supplier

XPath v XPath 는 W 3 C XML Path Language v XPath를 이용 XML 문서를 Root 네비게이션, 검색, 조작 v XPath는 DOM, XQuery, <Plants> XSL, XSLT과 같이 사용됨 "urn: nwtraders" <Plants xmlns="urn: nwtraders"> <? proc instr? > <!--comment--> "proc-instr" "comment " <Item. Name> <Item. Name code="123"> Clematis </Item. Name> </Plants> "urn: nwtraders" "123" "Clematis"

XPath v XPath 표준 navigation language v XML 계층구조로부터 XPath 쿼리로 노드를 리턴 Order/Order. Detail n 모든 Order. Detail 요소들 n Product. ID 특성이 23인 Order. Detail 요소들 Order/Order. Detail[@Product. ID='23'] n Quantity 가 1 보다 큰 요소를 가진 Order. Detail 요소 Order/Order. Detail[Quantity > 1]

XSLT v XSLT 는 W 3 C XSL Transformations language v XPath와 같이 사용 v XML vocabularies와 포맷으로 변환하는데 사용 <Plant ID="3">Lemon</Plant> <Name>Lemon</Name> <SKU>3</SKU> </Plant> XML vocabulary변환 XML HTML XML을 HTML이나 text로 변환

XSLT & XPath <xsl: stylesheet version="1. 0" xmlns: xsl="http: //www. w 3. org/1999/XSL/Transform"> <xsl: template match="/"> 루트노드 <xsl: apply-templates select="employees/employee"> <employees>밑의 <employee> <xsl: sort select="name" /> <name>로 정렬 </xsl: apply-templates> </xsl: template> </xsl: stylesheet> XPath 로트노트로 부터 employee name을 리스트

SQL Server 2000에서의 XML 지원 v SQL Server Data를 XML로 처리하는 방법 v Transact-SQL v XML을 이용한 SQL Server Data 변경

XML을 처리하는 방법 v System. Data v System. Xml v T-SQL v Update. Gram/Bulk Load v SOAP/WSDL v SQLOLEDB/SQLXMLOLEDB v Managed v Biztalk v …, SQLXML Class

Transact-SQL v SELECT . . . FOR XML n 결과셋이 XML stream으로 리턴 n relational data를 XML로 검색 v Open. XML n XML stream을 rowset으로 리턴 n XML data를 relational tables로 Insert

XML을 이용한 SQL Server Data 변경 v XML Updategrams 추가, 수정, 삭제시 XML document을 사용 v XML Bulk Loader Component n COM component n XML data의 bulk-load시 n

FOR XML v FOR XML 구문 SELECT select_list FROM table_source WHERE search_condition FOR XML RAW | AUTO | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE 64] v XML Document Fragments <Order. ID="10248" Order. Date="07/04/1996"/> <Order. ID="10249" Order. Date="07/05/1996"/>

SQL Server Generate SELECT … FROM … WHERE … FOR XML MODE Query processor ROWSET to XML TDS/ Token XML

Using RAW Mode 레코드에 하나의 <row> element n 각 컬럼은 attibute로 처리 v 각각의 SELECT Order. ID, Order. Date FROM Orders FOR XML RAW <row Order. ID="10248" Order. Date="07/04/1996"/> <row Order. ID="10249" Order. Date="07/05/1996"/>

Using AUTO Mode 이름이 element의 이름 n 중첩을 지원, 컬럼은 attribute로 처리 n Alias로 이름 변경 가능 v 테이블 SELECT Order. ID, Order. Date FROM Orders FOR XML AUTO <Orders Order. ID="10248" Order. Date="07/04/1996"/> <Orders Order. ID="10249" Order. Date="07/05/1996"/>

ELEMENTS v 컬럼은 child element로 처리 SELECT Order. ID, Order. Date FROM Orders FOR XML AUTO, ELEMENTS <Orders> <Order. ID>10248</Order. ID> <Order. Date>07/04/1996</Order. Date> </Orders> <Order. ID>10249</Order. ID> <Order. Date>07/05/1996</Order. Date> </Orders>

예) Table Join SELECT Order. Form. Order. ID, Item. Product. ID Item. Quantity FROM Orders Order. Form JOIN [Order Details] Item ON Order. Form. Order. ID = Item. Order. ID ORDER BY Order. Form. Order. ID FOR XML RAW <row Order. ID="10248" Product. ID="1" Quantity="12"/> <row Order. ID="10248" Product. ID="42" Quantity="10"/> SELECT Order. Form. Order. ID, Item. Product. ID Item. Quantity FROM Orders Order. Form JOIN [Order Details] Item ON Order. Form. Order. ID = Item. Order. ID ORDER BY Order. Form. Order. ID FOR XML AUTO <Order. Form Order. ID="10248"> <Item Product. ID="1" Quantity="12"/> <Item Product. ID="42" Quantity="10"/> </Order. Form>

XMLDATA v XML-Data Reduced (XDR) Schema로 리턴 SELECT Order. ID, Order. Date FROM Orders FOR XML AUTO, XMLDATA <Schema name="Schema 1" xmlns="urn: schemas-microsoft-com: xml-data" xmlns: dt="urn: schemas-microsoftcom: datatypes"> <Element. Type name="Orders" content="empty“ model="closed"> <Attribute. Type name="Order. ID" dt: type="14"/> <Attribute. Type name="Order. Date" dt: type="date. Time"/> <attribute type="Order. ID"/> <attribute type="Order. Date"/> </Element. Type> </Schema>

Binary Data SELECT Employee. ID, Photo FROM Employees WHERE Employee. ID = 10 FOR XML AUTO <Employees Employee. ID="1" Photo="db. Object/Employees[@Emp. ID='1']/@Photo"/> SELECT Employee. ID, Photo FROM Employees WHERE Employee. ID = 1 FOR XML AUTO, BINARY BASE 64 <Employees Emp. ID="1" Photo="FRwv. AAIAAAANAA 4 AFAAh. AP////9 Ca. . . "/>

Using EXPLICIT Mode 만들기 위해 T-SQL 작성 v 원하는 XML 문서형태로 제어 v 범용테이블을 SELECT 1 AS Tag, NULL AS Parent, Order. ID AS [Invoice!1!Invoice. No], Order. Date AS [Invoice!1!Date!Element] FROM Orders WHERE Order. ID = 10248 FOR XML EXPLICIT <Invoice. No="10248"> <Date>1996 -07 -04 T 00: 00</Date> </Invoice>

범용 테이블 v XML 문서의 특정형식(행집합) n Tag, Parent 계층구조 결정 n Column이름은 element / attribute 로 매핑 Tag Parent Invoice!1!Date! Element 1996 -07 -04 T 00: 00 Line. Item!2! Product. ID NULL Line. Item!2 NULL Invoice!1! Invoice. No 10248 1 2 1 10248 NULL 11 2 1 10248 NULL 42 Queso Cabrales Singaporean … NULL

예) EXPLICIT v UNION ALL이용 SELECT 1 AS Tag, NULL AS Parent, Order. ID AS [Invoice!1!Invoice. No], Order. Date AS [Invoice!1!Date!Element], NULL AS [Line. Item!2!Product. ID], NULL AS [Line. Item!2] FROM Orders WHERE Order. ID=10248 UNION ALL SELECT 2 AS Tag, 1 AS Parent, OD. Order. ID, NULL, OD. Product. ID, P. Product. Name FROM [Order Details] OD JOIN Orders O ON OD. Order. ID=O. Order. ID JOIN Products P ON OD. Product. ID = P. Product. ID WHERE OD. Order. ID=10248 ORDER BY [Invoice!1!Invoice. No], [Line. Item!2!Product. ID] FOR XML EXPLICIT

OPENXML v OPENXML syntax n Row를 결정하기 위해 rowpattern parameter 사용 n 컬럼을 결정하기 위해 WITH 구문사용 n Attribute를 결정하기 위해 Flags parameter 사용 SELECT * FROM Open. XML (@idoc, 'order', 1) WITH (orderno integer, orderdatetime)

OPENXML 적용방법 v sp_xml_preparedocument으로 트리생성 v sp_xml_removedocument 메모리 해제 CREATE PROC Process. Order @doc NText AS DECLARE @idoc integer EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Process Document EXEC sp_xml_removedocument @idoc

예) Inserting Data v INSERT 구문 INSERT orders SELECT * FROM Open. XML (@idoc, 'order', 1) WITH (orderno integer, orderdatetime) v SELECT INTO 구문 SELECT * INTO neworders FROM Open. XML (@idoc, 'order', 1) WITH (orderno integer, orderdatetime)

Using rowpattern v Xpath를 사용 SELECT * FROM Open. XML (@idoc, 'order/lineitem', 1) WITH (productid integer, quantity integer, price money) SELECT * FROM Open. XML (@idoc, 'order/lineitem[@quantity>2]', 1) WITH (productid integer, quantity integer, price money)

Using Flags v attributes 또는 elements 지정하기 위해 사용 n 0 = default (attributes) n 1 = attributes n 2 = elements n 3 = attributes elements (1 + 2)

Using a Table Name v WITH 구문에 테이블 이름 사용 n 컬럼이름이 match n 데이터 타입이 호환 INSERT lineitems SELECT * FROM Open. XML (@idoc, 'order/lineitem', 3) WITH lineitems

Column Pattern v 계층구조에서 데이터를 처리하기 위해 Xpath 사용 v relative XPath 적용 INSERT lineitems SELECT * FROM Open. XML (@idoc, 'order/lineitem', 1) WITH (orderno integer '. . /@orderno', productid integer, --Default Mapping quantity integer '. /quantity', price money --Default Mapping)