1 / 8

Viewing relational data as XML

This guide discusses how to retrieve relational data from Microsoft SQL Server in XML format using the FOR XML clause. Different formatting options are explored, including RAW, AUTO, EXPLICIT, and PATH. Examples demonstrate how each output format differs in structure, showcasing the versatility of XML output for relational data. You'll learn to create XML documents that represent rows as elements, use table names as element names, and control output using XPath for complex structures. This is essential for applications needing XML data integration.

maxwell-hill
Télécharger la présentation

Viewing relational data as XML

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Viewing relational data as XML Using Microsoft SQL Server

  2. The ”FOR XML” clause • SELECT comes with a ”FOR XML” clause • Example • SELECT * FROM student FOR XML RAW; • Output is an XML document Viewing relational data as XML

  3. Formatting options • The XML output can be formatted in different ways • RAW • AUTO • EXPLICIT • PATH Viewing relational data as XML

  4. RAW formatting • Each row in the output becomes an XML element. • Element name is ’row’ • Table column names used as XML attribute names • Example, simple • SELECT * FROM student FOR XML RAW; • Output • <row studentID="1" studentname="John" /> • <row studentID="2" studentname="Liz" /> • Example, join SELECT d.departmentID, departmentName, teachername FROM department d join teacher t ON d.departmentID = t.departmentID FOR XML RAW • Output <row departmentID="1" departmentName="Computer science" teachername="Anders" /> <row departmentID="1" departmentName="Computer science" teachername="Peter L" /> <row departmentID="1" departmentName="Computer science" teachername="Poul H" /> <row departmentID="2" departmentName="Marketing" teachername="Lars" /> No hierarchy! Viewing relational data as XML

  5. AUTO formatting • Each row in the output becomes an XML element. • Table name used as XML element name • Example, simple • SELECT * FROM student FOR XML AUTO; • Output • <student studentID="1" studentname="John" /> • <student studentID="2" studentname="Liz" /> • Example, join SELECT d.departmentID, departmentName, teachername FROM department d join teacher t ON d.departmentID = t.departmentID FOR XML AUTO • Output <department departmentID="1" departmentName="Computer science"> <teacher teachername="Anders B" /> <teacher teachername="Peter L" /> … </department> <department departmentID="2" departmentName="Marketing"> <teacher teachername="Lars" /> <teacher teachername="Poul" /> </department> Viewing relational data as XML

  6. EXPLICIT formatting • Gives you a lot of control over the output • Element names, attribute names, etc. • Requires a lot of work! • Not used very often • May soon be deprecated Viewing relational data as XML

  7. PATH formatting • A better way of doing EXPLICIT • Used for complex XML output • Based on XPath • Example, simple • SELECT * FROM student FOR XML PATH; • Output • <row> • <studentID>1</studentID> • <studentname>John</studentname> • </row> • Each row becomes an element (like RAW) • Each column becomes a child-element (unlike RAW) Viewing relational data as XML

  8. PATH formatting, continued • Example • SELECT studentID as '@studentid', studentname FROM student FOR XML PATH; • Output • <row studentid="1"> • <studentname>John</studentname> • </row> • <row studentid="2"> • <studentname>Liz</studentname> • </row> • Columns names @xx becomes attributes in the XML output • And much more XPath stuff … Viewing relational data as XML

More Related