SQLTeam.com | Weblogs | Forums

For xml


#1

I'm creating xml with select statement FOR XML RAW(''ASSISST'),ROOT('ROWSET') ELEMENTS.

I would like to add values to ROWSET element:

this is my xml:

<ROWSET>
 <ASISTENCIJA>
   <BrojPolice>1444-30735655</BrojPolice>
   <VrstaPolice>AO</VrstaPolice>
   <DatumIzdavanja>14.04.2016</DatumIzdavanja>
 </ASISTENCIJA>
</ROWSET>

#2

This?

create table #Hrcko(BrojPolice varchar(50), 
                             VrstaPolice varchar(50), 
DatumIzdavanja varchar(50))

insert into #Hrcko
select '1444-30735655', 'AO', '14.04.2016' union
select '1444-30735656', 'AP', '14.04.2016' union
select '1444-30735657', 'AS', '14.04.2016' 

select BrojPolice, VrstaPolice, DatumIzdavanja
  from  #Hrcko 
  FOR XML PATH ('ASISTENCIJA'), ROOT('ROWSET') ;  

drop table #Hrcko

<ROWSET>
  <ASISTENCIJA>
    <BrojPolice>1444-30735655</BrojPolice>
    <VrstaPolice>AO</VrstaPolice>
    <DatumIzdavanja>14.04.2016</DatumIzdavanja>
  </ASISTENCIJA>
  <ASISTENCIJA>
    <BrojPolice>1444-30735656</BrojPolice>
    <VrstaPolice>AP</VrstaPolice>
    <DatumIzdavanja>14.04.2016</DatumIzdavanja>
  </ASISTENCIJA>
  <ASISTENCIJA>
    <BrojPolice>1444-30735657</BrojPolice>
    <VrstaPolice>AS</VrstaPolice>
    <DatumIzdavanja>14.04.2016</DatumIzdavanja>
  </ASISTENCIJA>
</ROWSET>

#3

There is one more problem.
I can't create xml file when more then one child node. Why?
This is my code for xml file:
DECLARE @command VARCHAR(8000) = 'echo ' + @xmlChar +' > \hrzgtest02\Oryx\xml' + cast(year(getdate()) as varchar) +
cast(month(GETDATE()) as varchar) + cast(day(getdate()) as varchar) + cast(@brPrijenosa as varchar) + '.xml'

@xmlChar is above select


#4

is this a new question or the original solution provided is not working as desired. provide more details