1 / 8

Viewing relational data as XML

Viewing relational data as XML. Using Microsoft SQL Server. The ”FOR XML” clause. SELECT comes with a ”FOR XML” clause Example SELECT * FROM student FOR XML RAW ; Output is an XML document. Formatting options. The XML output can be formatted in different ways RAW AUTO EXPLICIT PATH.

maxwell-hill
Download Presentation

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