-- select * from Ozenka
-- for xml auto
--select * from Ozenka
--for xml raw('ozenka'), elements
--select * from Ozenka
--for xml auto, root

--select * from  Student, Ozenka
--for xml auto, root('Student')

--select * from Ozenka
--for xml raw('aa'),elements xsinil


--select ball as '@ball',
--	Student.year as 'student/@year',
--	Student.fio as 'student/fio',
--	vuz as 'student/vuz',
--	subject as 'subject'
--from Ozenka,Student
--where stud=fio
--for xml path('studentball'),root('studentballs')


declare @x xml
set @x = '<studentballs>
  <studentball ball="4">
    <student year="2014">
      <fio>Petrov</fio>
      <vuz>MGU</vuz>
    </student>
    <subject>Math</subject>
  </studentball>
  <studentball ball="3">
    <student year="2013">
      <fio>Sidorov</fio>
      <vuz>MGU</vuz>
    </student>
    <subject>Physics</subject>
  </studentball>
  <studentball ball="3">
    <student year="2014">
      <fio>Petrov</fio>
      <vuz>MGTU</vuz>
    </student>
  </studentball>
</studentballs>'

declare @doc int
exec sp_xml_preparedocument @doc output,@x

--select * from openxml(@doc,'/studentballs/studentball/student')
--with(fio varchar(15) 'fio')

select * from openxml(@doc,'/studentballs/studentball/student')
with(fio varchar(15) 'fio',
subject varchar(15) '../subject',
ball int '../@ball')

--select DISTINCT * from openxml(@doc,'/studentballs/studentball/student')
--with(vuz varchar(10) 'vuz') where vuz='MGU'