Viewing relational data as XML Using Microsoft SQL Server.

  • Published on
    23-Dec-2015

  • View
    215

  • Download
    3

Transcript

  • Slide 1
  • Viewing relational data as XML Using Microsoft SQL Server
  • Slide 2
  • The FOR XML clause SELECT comes with a FOR XML clause Example SELECT * FROM student FOR XML RAW; Output is an XML document 2Viewing relational data as XML
  • Slide 3
  • Formatting options The XML output can be formatted in different ways RAW AUTO EXPLICIT PATH 3Viewing relational data as XML
  • Slide 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 Example, join SELECT d.departmentID, departmentName, teachername FROM department d join teacher t ON d.departmentID = t.departmentID FOR XML RAW Output No hierarchy! 4Viewing relational data as XML
  • Slide 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 Example, join SELECT d.departmentID, departmentName, teachername FROM department d join teacher t ON d.departmentID = t.departmentID FOR XML AUTO Output 5Viewing relational data as XML
  • Slide 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 XML6
  • Slide 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 1 John Each row becomes an element (like RAW) Each column becomes a child-element (unlike RAW) Viewing relational data as XML7
  • Slide 8
  • PATH formatting, continued Example SELECT studentID as '@studentid', studentname FROM student FOR XML PATH; Output John Liz Columns names @xx becomes attributes in the XML output And much more XPath stuff Viewing relational data as XML8

Recommended

View more >