1 / 67

C10. SQL-S + XML

C10. SQL-S + XML. Date Semistructurate, 2012-2013. C 10 . DS. SQL-S + XML. C10 - DS. FOR Clause [ FOR { BROWSE | < XML > } ] <XML> ::= XML { { RAW [ ( 'ElementName' ) ] | AUTO } [ <CommonDirectives> [ , { XMLDATA | XMLSCHEMA [ ( ‘NS_URI‘ ) ] } ]

newton
Download Presentation

C10. SQL-S + 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. C10. SQL-S + XML Date Semistructurate, 2012-2013

  2. C10. DS • SQL-S + XML

  3. C10 - DS • FOR Clause [ FOR { BROWSE | <XML> } ] <XML> ::= XML { { RAW [ ( 'ElementName' ) ] | AUTO } [ <CommonDirectives> [ , { XMLDATA | XMLSCHEMA [ ( ‘NS_URI‘ ) ] } ] [ , ELEMENTS [ XSINIL ] ]   | EXPLICIT [ <CommonDirectives> [ , XMLDATA ] ] | PATH [ ( 'ElementName' ) ] [ <CommonDirectives> [ , ELEMENTS [ XSINIL ] ] ] } <CommonDirectives> ::= [ , TYPE ] [ , ROOT [ ( 'RootName' ) ] ]

  4. C10 - DS • BD Exemple: • sectii (cods, denumires) • discipline (codd, denumired, detalii) • planinv (cods, codd) • studenti (cods, nrmatricol, nume, grupa, datan, medie) • rezultate (nrmatricol, disciplina, nota) Obs: Discipline.detalii este de tip XML, cu element radacina <detalii>, si in exemplele prezentate, contine elemente <prof>; exemplu: <detalii> <prof>prof de inele</prof> <prof>prof de corpuri</prof> </detalii>

  5. C10 - DS • XML - Specifies that the results of a query are to be returned as an XML document. One of the following XML modes must be specified: RAW, AUTO, EXPLICIT. • RAW [ ('ElementName') ] - Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. You can optionally specify a name for the row element. The resulting XML output uses the specified ElementName as the row element generated for each row. • AUTO - Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes. • EXPLICIT - Specifies that the shape of the resulting XML tree is defined explicitly. Using this mode, queries must be written in a particular way so that additional information about the desired nesting is specified explicitly.

  6. C10 - DS • XMLDATA - Returns inline XDR schema, but does not add the root element to the result. If XMLDATA is specified, XDR schema is appended to the document. • XMLSCHEMA [ ('TargetNameSpaceURI') ] - Returns inline XSD schema. You can optionally specify a target namespace URI when you specify this directive, which returns the specified namespace in the schema. • ELEMENTS - Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in RAW, AUTO and PATH modes only. You can optionally specify XSINIL or ABSENT when you use this directive. XSINIL specifies that an element that has an xsi:nil attribute set to True be created for NULL column values. By default or when ABSENT is specified together with ELEMENTS, no elements are created for NULL values. • XSINIL - Specifies that an element with xsi:nil attribute set to True be created for NULL column values. This option can only be specified with ELEMENTS directive.

  7. C10 - DS • PATH [ ('ElementName') ] - Generates a <row> element wrapper for each row in the result set. You can optionally specify an element name for the <row> element wrapper. If an empty string is provided, such as FOR XML PATH ('') ), a wrapper element is not generated. Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive. • You can use FOR XML EXPLICIT mode queries to construct this kind of XML from a rowset, but the PATH mode provides a simpler alternative to the possibly cumbersome EXPLICIT mode queries. PATH mode, together with the ability to write nested FOR XML queries and the TYPE directive to return xml type instances, allows you to write queries with less complexity. It provides an alternative to writing most EXPLICIT mode queries. By default, PATH mode generates a <row> element wrapper for each row in the result set. You can optionally specify an element name. If you do, the specified name is used as the wrapper element name. If you provide an empty string (FOR XML PATH ('')), no wrapper element is generated.

  8. C10 - DS • TYPE • Specifies that the query returns results as xml type. • ROOT [ ('RootName') ] • Specifies that a single top-level element be added to the resulting XML. You can optionally specify the root element name to generate. If the optional root name is not specified, the default <root> element is added.

  9. C10 - DS • Using RAW Mode • RAW mode transforms each row in the query result set into an XML element that has the generic identifier <row>, or the optionally provided element name. • By default, each column value in the rowset that is not NULL is mapped to an attribute of the <row> element. • If the ELEMENTS directive is added to the FOR XML clause, each column value is mapped to a subelement of the <row> element. Together with the ELEMENTS directive, you can optionally specify the XSINIL option to map NULL column values in the result set to an element that has the attribute, xsi:nil="true".

  10. C10 - DS • Exemple RAW select * from studenti for xml raw => <row CodS="4" NrMatricol="110" Nume="Lupu Teodor Grigore" Grupa="132" DataN="2001-01-01T00:00:00" /> <row CodS="4" NrMatricol="120" Nume="Maguran Florin-Ciprian" Grupa="132" DataN="2006-02-01T00:00:00" /> <row CodS="4" NrMatricol="140" Nume="Milasan Cristian Dorel" Grupa="132" DataN="2003-02-05T00:00:00" /> <row CodS="4" NrMatricol="150" Nume="Nagy Alina Ioana" Grupa="122" DataN="1990-12-02T00:00:00" /> <row CodS="6" NrMatricol="152" Nume="Hodis George-Raul" Grupa="821" /> <row CodS="4" NrMatricol="154" Nume="Farcas Vasile-Sebastian" Grupa="342" Medie="7.00" /> (...)

  11. C10 - DS • Exemple RAW select * from studenti for xml raw, elements => <row> <CodS>4</CodS> <NrMatricol>110</NrMatricol> <Nume>Lupu Teodor Grigore</Nume> <Grupa>132</Grupa> <DataN>2001-01-01T00:00:00</DataN> </row> <row> <CodS>6</CodS> <NrMatricol>152</NrMatricol> <Nume>Hodis George-Raul</Nume> <Grupa>821</Grupa> </row> <row> <CodS>4</CodS> <NrMatricol>154</NrMatricol> <Nume>Farcas Vasile-Sebastian</Nume> <Grupa>342</Grupa> <Medie>7.00</Medie> </row> (...)

  12. C10 - DS <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <CodS>4</CodS> <NrMatricol>110</NrMatricol> <Nume>Lupu Teodor Grigore</Nume> <Grupa>132</Grupa> <DataN>2001-01-01T00:00:00</DataN> <Medie xsi:nil="true" /> </row> <row xmlns:xsi="..."> <CodS>6</CodS> <NrMatricol>152</NrMatricol> <Nume>Hodis George-Raul</Nume> <Grupa>821</Grupa> <DataN xsi:nil="true" /> <Medie xsi:nil="true" /> </row> <row xmlns:xsi="..."> <CodS>4</CodS> <NrMatricol>154</NrMatricol> <Nume>Farcas Vasile-Sebastian</Nume> <Grupa>342</Grupa> <DataN xsi:nil="true" /> <Medie>7.00</Medie> </row> (...) • Exemple RAW select * from studenti for xml raw, elements xsinil =>

  13. C10 - DS • Exemple RAW select * from studenti for xml raw('student') => <student CodS="4" NrMatricol="110" Nume="Lupu Teodor Grigore" Grupa="132" DataN="2001-01-01T00:00:00" /> <student CodS="6" NrMatricol="152" Nume="Hodis George-Raul" Grupa="821" /> <student CodS="4" NrMatricol="154" Nume="Farcas Vasile-Sebastian" Grupa="342" Medie="7.00" /> (...)

  14. C10 - DS <student> <CodS>4</CodS> <NrMatricol>110</NrMatricol> <Nume>Lupu Teodor Grigore</Nume> <Grupa>132</Grupa> <DataN>2001-01-01T00:00:00</DataN> </student> <student> <CodS>6</CodS> <NrMatricol>152</NrMatricol> <Nume>Hodis George-Raul</Nume> <Grupa>821</Grupa> </student> <student> <CodS>4</CodS> <NrMatricol>154</NrMatricol> <Nume>Farcas Vasile-Sebastian</Nume> <Grupa>342</Grupa> <Medie>7.00</Medie> </student> (...) • Exemple RAW select * from studenti for xml raw('student'), elements =>

  15. C10 - DS • Exemple RAW select * from studenti for xml raw('student'), root('facultate') => <facultate> <student CodS="4" NrMatricol="110" Nume="Lupu Teodor Grigore" Grupa="132" DataN="2001-01-01T00:00:00" /> <student CodS="6" NrMatricol="152" Nume="Hodis George-Raul" Grupa="821" /> <student CodS="4" NrMatricol="154" Nume="Farcas Vasile-Sebastian" Grupa="342" Medie="7.00" /> (...) </facultate>

  16. C10 - DS • Exemple RAW select CodD, DenumireD, DetaliiDisc = detalii from discipline for xml raw => <row CodD="MA001" DenumireD="Structuri algebrice de baza"> <DetaliiDisc> <detalii> <prof>prof de algebra 1</prof> <prof>prof de algebra 2</prof> </detalii> </DetaliiDisc> </row> <row CodD="MA002" DenumireD="Algebra liniara" /> <row CodD="MA003" DenumireD="Inele si corpuri"> <DetaliiDisc> <detalii> <prof>prof de inele</prof> <prof>prof de corpuri</prof> </detalii> </DetaliiDisc> </row> (…)

  17. C10 - DS • Exemple RAW select CodD, DenumireD, DetaliiDisc = detalii from discipline for xml raw, elements => <row> <CodD>MA001</CodD> <DenumireD>Structuri algebrice de baza</DenumireD> <DetaliiDisc> <detalii> <prof>prof de curs structuri</prof> <prof>prof de seminar structuri</prof> </detalii> </DetaliiDisc> </row> <row> <CodD>MA002</CodD> <DenumireD>Algebra liniara</DenumireD> </row>(…)

  18. C10 - DS • Exemple RAW select se.CodS, se.DenumireS, st.NrMatricol, st.Nume from sectii as se inner join studenti as st on st.CodS = se.CodS order by se.cods for xml raw => <row CodS="1" DenumireS="Matematicã" NrMatricol="7587" Nume="Huluban Ovidiu" /> <row CodS="1" DenumireS="Matematicã" NrMatricol="8074" Nume="Vilceanu Veronica Aurelia" /> (...) <row CodS="2" DenumireS="Informaticã" NrMatricol="8791" Nume="Dunca Petru Stefan" /> <row CodS="2" DenumireS="Informaticã" NrMatricol="8793" Nume="Ferenczi-Tarta Silviu-Sever" /> (...)

  19. C10 - DS • Exemple RAW select se.CodS, se.DenumireS, studenti = (select st.NrMatricol, st.Nume from studenti as st where st.CodS = se.CodS for xml raw ('student')) from sectii as se order by se.cods for xml raw, elements=> <row> <CodS>1</CodS> <DenumireS>Matematica</DenumireS> <studenti>&lt;student NrMatricol="7587" Nume="Huluban Ovidiu"/&gt;&lt;student NrMatricol="8043" Nume="Pop Daniela Ioana"/&gt;&lt;student NrMatricol="8070" Nume="Ulici Amalia Laura"/&gt;&lt;student NrMatricol="8074" Nume="Vilceanu Veronica Aureli"/&gt; (...)</studenti> </row> (...)

  20. C10 - DS • Exemple RAW select se.CodS, se.DenumireS, studenti = (select st.NrMatricol, st.Nume from studenti as st where st.CodS = se.CodS for xml raw ('student'), type) from sectii as se order by se.cods for xml raw, elements=> <row> <CodS>1</CodS> <DenumireS>Matematica</DenumireS> <studenti> <student NrMatricol="7587" Nume="Huluban Ovidiu" /> <student NrMatricol="8043" Nume="Pop Daniela Ioana" /> <student NrMatricol="8070" Nume="Ulici Amalia Laura" /> <student NrMatricol="8074" Nume="Vilceanu Veronica Aureli" /> (...) </studenti> </row> (...)

  21. C10 - DS • Using AUTO Mode • AUTO mode returns query results as nested XML elements. This does not provide much control over the shape of the XML generated from a query result. The AUTO mode queries are useful if you want to generate simple hierarchies. • Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified in the FOR XML clause.

  22. C10 - DS • Using AUTO Mode • The XML hierarchy, nesting of the elements, in the resulting XML is based on the order of tables identified by the columns specified in the SELECT clause. Therefore, the order in which column names are specified in the SELECT clause is significant. The first, leftmost table that is identified forms the top element in the resulting XML document. The second leftmost table, identified by columns in the SELECT statement, forms a subelement within the top element, and so on. • If a column name listed in the SELECT clause is from a table that is already identified by a previously specified column in the SELECT clause, the column is added as an attribute of the element already created, instead of opening a new level of hierarchy. If the ELEMENTS option is specified, the column is added as an element.

  23. C10 - DS • Using AUTO Mode • When a column in the SELECT clause cannot be associated with any of the tables identified in the FROM clause, as in the case of an aggregate column or computed column, the column is added in the XML document in the deepest nesting level in place when it is encountered in the list. If such a column appears as the first column in the SELECT clause, the column is added to the top element.

  24. C10 - DS • ExempleAUTO select * from studenti for xml auto => <studenti CodS="4" NrMatricol="110" Nume="Lupu Teodor Grigore" Grupa="132" DataN="2001-01-01T00:00:00" /> <studenti CodS="6" NrMatricol="152" Nume="Hodis George-Raul" Grupa="821" /> <studenti CodS="4" NrMatricol="154" Nume="Farcas Vasile-Sebastian" Grupa="342" Medie="7.00" /> (...)

  25. C10 - DS <facultate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <studenti> <CodS>4</CodS> <NrMatricol>110</NrMatricol> <Nume>Lupu Teodor Grigore</Nume> <Grupa>132</Grupa> <DataN>2001-01-01T00:00:00</DataN> <Medie xsi:nil="true" /> </studenti> <studenti> <CodS>6</CodS> <NrMatricol>152</NrMatricol> <Nume>Hodis George-Raul</Nume> <Grupa>821</Grupa> <DataN xsi:nil="true" /> <Medie xsi:nil="true" /> </studenti> <studenti> <CodS>4</CodS> <NrMatricol>154</NrMatricol> <Nume>Farcas Vasile-Sebastian</Nume> <Grupa>342</Grupa> <DataN xsi:nil="true" /> <Medie>7.00</Medie> </studenti> (...) • ExempleAUTO select * from studenti for xml auto, root('facultate'), elements xsinil =>

  26. C10 - DS • ExempleAUTO select se.CodS, se.DenumireS, st.NrMatricol, st.Nume from sectii as se inner join studenti as st on st.CodS = se.CodS for xml auto => <se CodS="4" DenumireS="Matematicã economicã"> <st NrMatricol="110" Nume="Lupu Teodor Grigore" /> <st NrMatricol="120" Nume="Maguran Florin-Ciprian" /> <st NrMatricol="140" Nume="Milasan Cristian Dorel" /> </se> <se CodS="6" DenumireS="Tehnologie Informaticã"> <st NrMatricol="146" Nume="Chis Radu Ioab" /> </se> <se CodS="4" DenumireS="Matematicã economicã"> <st NrMatricol="150" Nume="Nagy Alina Ioana" /> </se> <se CodS="6" DenumireS="Tehnologie Informaticã"> <st NrMatricol="152" Nume="Hodis George-Raul" /> </se> (...)

  27. C10 - DS • ExempleAUTO select se.CodS, se.DenumireS, st.NrMatricol, st.Nume from sectii as se inner join studenti as st on st.CodS = se.CodS order by se.CodS for xml auto => <se CodS="1" DenumireS="Matematicã"> <st NrMatricol="7587" Nume="Huluban Ovidiu" /> <st NrMatricol="8074" Nume="Vilceanu Veronica Aureli" /> <st NrMatricol="8507" Nume="Mocan Ionela" /> (...) </se> <se CodS="2" DenumireS="Informaticã"> <st NrMatricol="8791" Nume="Dunca Petru Stefan" /> <st NrMatricol="8793" Nume="Ferenczi-Tarta Silviu-Sever" /> <st NrMatricol="8830" Nume="Muresan Horatiu" /> <st NrMatricol="8832" Nume="Musetescu Mircea" /> (...)

  28. C10 - DS • ExempleAUTO select se.CodS, se.DenumireS, st.NrMatricol, st.Nume, r.disciplina, r.nota from sectii as se inner join studenti as st on st.CodS = se.CodS inner join rezultate as r on r.NrMatricol = st.NrMatricol order by se.cods, st.nrmatricol for xml auto, root('facultate') =>

  29. C10 - DS • ExempleAUTO (rezultatul exemplului anterior) <facultate> <se CodS="1" DenumireS="Matematicã"> <st NrMatricol="7587" Nume="Huluban Ovidiu"> <r disciplina="MA001" nota="9.00" /> <r disciplina="MI074" nota="8.00" /> </st> <st NrMatricol="8043" Nume="Pop Daniela Ioana"> <r disciplina="MG001" nota="7.00" /> </st> (...) </se> <se CodS="2" DenumireS="Informaticã"> <st NrMatricol="7019" Nume="Damian Mircea"> <r disciplina="MA002" nota="7.00" /> <r disciplina="MA001" nota="9.00" /> </st> <st NrMatricol="7255" Nume="Iurcu Florin-Daniel"> <r disciplina="MA001" nota="8.00" /> </st> (...)

  30. C10 - DS • ExempleAUTO select se.CodS, se.DenumireS, media = avg(r.nota), st.NrMatricol, st.Nume from sectii as se inner join studenti as st on st.CodS = se.CodS inner join rezultate as r on r.NrMatricol = st.NrMatricol group by se.CodS, se.DenumireS, st.NrMatricol, st.Nume order by se.cods, st.nrmatricol for xml auto, root('facultate') => <facultate> <se CodS="1" DenumireS="Matematicã" media="8.500000"> <st NrMatricol="7587" Nume="Huluban Ovidiu" /> </se> <se CodS="1" DenumireS="Matematicã" media="7.000000"> <st NrMatricol="8043" Nume="Pop Daniela Ioana" /> </se> <se CodS="1" DenumireS="Matematicã" media="9.000000"> <st NrMatricol="9098" Nume="Daraban Gheorghe-Ciprian" /> <st NrMatricol="9118" Nume="Ghirean Anca-Raluca" /> </se> (...)

  31. C10 - DS • ExempleAUTO select se.CodS, se.DenumireS, st.NrMatricol, st.Nume, media = avg(r.nota) from sectii as se inner join studenti as st on st.CodS = se.CodS inner join rezultate as r on r.NrMatricol = st.NrMatricol group by se.CodS, se.DenumireS, st.NrMatricol, st.Nume order by se.cods, st.nrmatricol for xml auto, root('facultate') =>

  32. C10 - DS • ExempleAUTO (rezultatul exemplului anterior) <facultate> <se CodS="1" DenumireS="Matematicã"> <st NrMatricol="7587" Nume="Huluban Ovidiu" media="8.500000" /> <st NrMatricol="8043" Nume="Pop Daniela Ioana" media="7.000000" /> <st NrMatricol="9098" Nume="Daraban Gheorghe-Ciprian" media="9.000000" /> <st NrMatricol="9118" Nume="Ghirean Anca-Raluca" media="9.000000" /> (...) </se> <se CodS="2" DenumireS="Informaticã"> <st NrMatricol="7019" Nume="Damian Mircea" media="8.000000" /> <st NrMatricol="7255" Nume="Iurcu Florin-Daniel" media="8.000000" /> <st NrMatricol="7908" Nume="Ilovan Cristian Marian" media="10.000000" /> </se> <se CodS="3" DenumireS="Matematicã-Informaticã"> <st NrMatricol="525" Nume="Anghelcev Daniel" media="8.500000" /> </se> (...)

  33. C10 - DS • Using EXPLICIT Mode • The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. • Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.

  34. C10 - DS • Using EXPLICIT Mode • The EXPLICIT mode transforms the rowset that results from the query execution into an XML document. In order for EXPLICIT mode to produce the XML document, the rowset must have a specific format. This requires that you write the SELECT query to produce the rowset, the universal table, with a specific format so the processing logic can then produce the XML you want. • First, the query must produce the following two metadata columns: • The first column must provide the tag number, integer type, of the current element, and the column name must be Tag. Your query must provide a unique tag number for each element that will be constructed from the rowset. • The second column must provide a tag number of the parent element, and this column name must be Parent. In this way, the Tag and the Parent column provide hierarchy information.

  35. C10 - DS • Using EXPLICIT Mode • Exemplu: universal table <Customer cid="C1" name="Janine"> <Order id="O1" date="1/20/1996"> <OrderDetail id="OD1" pid="P1"/> <OrderDetail id="OD2" pid="P2"/> </Order> <Order id="O2" date="3/29/1997"> </Customer>

  36. C10 - DS • Using EXPLICIT Mode • Specifying Column Names in a Universal Table • When writing EXPLICIT mode queries, column names in the resulting rowset must be specified by using this format. They provide transformation information including element and attribute names and other additional information, specified by using directives. • This is the general format: • ElementName!TagNumber!AttributeName!Directive • Following is the description of the parts of the format. • ElementName • Is the resulting generic identifier of the element. For example, if Customers is specified as ElementName, the <Customers> element is generated.

  37. C10 - DS • Using EXPLICIT Mode • TagNumber • Is a unique tag value assigned to an element. This value, with the help of the two metadata columns, Tag and Parent, determines the nesting of the elements in the resulting XML. • AttributeName • Provides the name of the attribute to construct in the specified ElementName. This is the behavior if Directive is not specified. • If Directive is specified and it is xml, cdata, or element, this value is used to construct an element child of ElementName, and the column value is added to it. • If you specify the Directive, the AttributeName can be empty. For example, ElementName!TagNumber!!Directive. In this case, the column value is directly contained by the ElementName.

  38. C10 - DS • Using EXPLICIT Mode • Directive • Directive is optional. • You can use Directive to indicate how to map the string data to XML. The hide, element, elementxsinil, xml, xmltext, and cdata keywords can be used as the Directive. The hide directive hides the node. This is useful when you retrieve values only for sorting purposes, but you do not want them in the resulting XML. • The element directive generates a contained element instead of an attribute. For NULL column values, no element is generated. If you want an element generated for null column values, you can specify the elementxsinil directive. This will generate an element that has the attribute xsi:nil=TRUE.

  39. C10 - DS • Using EXPLICIT Mode • If the Directive and the AttributeName is not specified, for example, Customer!1, an element directive is implied, such as Customer!1!!element, and column data is contained in the ElementName. • If AttributeName is specified, the tag name is replaced by the specified name. Otherwise, the attribute is appended to the current list of attributes of the enclosing elements by putting the content at the beginning of the containment without entity encoding. The column with this directive must be a text type, such as varchar, nvarchar, char, nchar, text, or ntext. This directive can be used only with hide.

  40. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires] from sectii => tag parent sectie!1!codsectie sectie!1!denumires ------ ----------- ------------------ -------------------------- 1 NULL 1 Matematicã 1 NULL 2 Informaticã 1 NULL 3 Matematicã-Informaticã 1 NULL 4 Matematicã economicã 1 NULL 5 Matematici aplicate 1 NULL 6 Tehnologie Informaticã

  41. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires] from sectii for xml explicit => <sectie codsectie="1" denumires="Matematicã" /> <sectie codsectie="2" denumires="Informaticã" /> <sectie codsectie="3" denumires="Matematicã-Informaticã" /> <sectie codsectie="4" denumires="Matematicã economicã" /> <sectie codsectie="5" denumires="Matematici aplicate" /> <sectie codsectie="6" denumires="Tehnologie Informaticã" />

  42. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires!element] from sectii for xml explicit => <sectie codsectie="1"> <denumires>Matematicã</denumires> </sectie> <sectie codsectie="2"> <denumires>Informaticã</denumires> </sectie> <sectie codsectie="3"> <denumires>Matematicã-Informaticã</denumires> </sectie> (...)

  43. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires], null as [student!2!nrmatricol], null as [student!2!nume], null as [student!2!grupa] from sectii union all select 2 as tag, 1 as parent, cods as [sectie!1!codsectie], null as [sectie!1!denumires], nrmatricol as [student!2!nrmatricol], nume as [student!2!nume], grupa as [student!2!grupa] from studenti

  44. C10 - DS • ExempleEXPLICIT =>

  45. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires], null as [student!2!nrmatricol], null as [student!2!nume], null as [student!2!grupa] from sectii union all select 2 as tag, 1 as parent, cods as [sectie!1!codsectie], null as [sectie!1!denumires], nrmatricol as [student!2!nrmatricol], nume as [student!2!nume], grupa as [student!2!grupa] from studenti for xml explicit

  46. C10 - DS • ExempleEXPLICIT (rezultat exemplul anterior) <sectie codsectie="1" denumires="Matematicã" /> <sectie codsectie="2" denumires="Informaticã" /> <sectie codsectie="3" denumires="Matematicã-Informaticã" /> <sectie codsectie="4" denumires="Matematicã economicã" /> <sectie codsectie="5" denumires="Matematici aplicate" /> <sectie codsectie="6" denumires="Tehnologie Informaticã"> <student nrmatricol="110" nume="Lupu Teodor Grigore" grupa="132" /> <student nrmatricol="120" nume="Maguran Florin-Ciprian" grupa="132" /> <student nrmatricol="140" nume="Milasan Cristian Dorel" grupa="132" /> <student nrmatricol="146" nume="Chis Radu Ioab" grupa="821" /> <student nrmatricol="150" nume="Nagy Alina Ioana" grupa="122" /> <student nrmatricol="152" nume="Hodis George-Raul" grupa="821" /> (...) </sectie>

  47. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires], null as [student!2!nrmatricol], null as [student!2!nume], null as [student!2!grupa] from sectii union all select 2 as tag, 1 as parent, cods as [sectie!1!codsectie], null as [sectie!1!denumires], nrmatricol as [student!2!nrmatricol], nume as [student!2!nume], grupa as [student!2!grupa] from studenti order by [sectie!1!codsectie], [sectie!1!denumires] desc for xml explicit

  48. C10 - DS • ExempleEXPLICIT =>

  49. C10 - DS • ExempleEXPLICIT (rezultat exemplul anterior) <sectie codsectie="1" denumires="Matematicã"> <student nrmatricol="7587" nume="Huluban Ovidiu" grupa="141" /> <student nrmatricol="8507" nume="Mocan Ionela" grupa="141" /> (...) </sectie> <sectie codsectie="2" denumires="Informaticã"> <student nrmatricol="8687" nume="Aflat Raluca Ioana" grupa="232" /> <student nrmatricol="8072" nume="Bucsa Iuliu Catalin" grupa="225" /> (...) </sectie> <sectie codsectie="3" denumires="Matematicã-Informaticã"> <student nrmatricol="175" nume="Banc Ioana Maria" grupa="341" /> <student nrmatricol="183" nume="Codarcea Petru-Constantin" grupa="341" /> (...)

  50. C10 - DS • ExempleEXPLICIT select 1 as tag, null as parent, cods as [sectie!1!codsectie], denumires as [sectie!1!denumires], null as [student!2!nrmatricol], null as [student!2!nume], null as [student!2!grupa], null as [rezultat!3!disciplina], null as [rezultat!3!nota] from sectii union all select 2 as tag, 1 as parent, cods as [sectie!1!codsectie], null as [sectie!1!denumires], nrmatricol as [student!2!nrmatricol], nume as [student!2!nume], grupa as [student!2!grupa], null as [rezultat!3!disciplina], null as [rezultat!3!nota] from studenti union all select 3 as tag, 2 as parent, st.cods as [sectie!1!codsectie], null as [sectie!1!denumires], r.nrmatricol as [student!2!nrmatricol], null as [student!2!nume], null as [student!2!grupa], disciplina as [rezultat!3!disciplina], nota as [rezultat!3!nota] from studenti as st inner join rezultate as r on r.nrmatricol = st.nrmatricol order by [sectie!1!codsectie], [sectie!1!denumires] desc, [student!2!nrmatricol], [student!2!nume] desc for xml explicit

More Related