Creating FOR XML RAW xml with multiple lines..

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  March 8, 2018 at 7:17 am

  #333848

  Hello all..
  I was trying to create an XML which has following design..

  <?xml version="1.0"?>
<MAIN-file>
<file name=id type= type size= size/>
<description> [description]</description>
<source type=stype id= [sourceid]/>
<destination type=[accesstype1] id=“[accessid]”/>
<destination type=[accesstype2] id=" [accessid]2” />
<destination type="[accesstype3]" id="[accessid]3”/>
<category name="NEW" </category>
</MAIN-file>

  but it has multiple lines with different tags, (elements of XML are in  same row)
  I was able to get 1st line done with one FOR XML RAW code but not able to get other lines merged..
  I have created a sample data here:

  IF EXISTS(SELECT *    FROM ##temp)
DROP TABLE ##temp

  CREATE TABLE ##temp
(
    [id] varchar(10) NULL,
    [type] [varchar](3)  NULL,
     [varchar](1)  NULL,
    [description] [varchar](100) NULL,
    [stype] [varchar](6)  NULL,
    [sourceid] [varchar](9)  NULL,
    [accesstype] [varchar](13)  NULL,
    [accessid] varchar(14) NULL,
    [Category name] [varchar](3)  NULL
)

  insert into ##temp

  select '4324'    , 'xls' ,    '0',     'SOB'    ,'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
select '4325'    , 'xls' ,    '0',     'SOB' ,    'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
select '4698'    , 'xls' ,    '0',     'SOC' ,    'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
select '4219'    , 'xls' ,    '0',     'COC' ,    'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
select '4327'    , 'xls' ,    '0',     'SOB' ,    'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
select '4326'    , 'xls' ,    '0',     'SOB' ,     'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
select '4092'    , 'xls' ,    '0',     'RMD',    'source'    ,'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
select '4037'    , 'xls' ,    '0',     'SOB'    , 'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW'

  select * from ##temp

  SELECT id, type , size
FROM ##temp
FOR XML RAW ('file'), ROOT ('main-file') ;

  -- <?xml version="1.0"?>
--<MAIN-file>
-- <file name=id type= type size= size/>
-- <description> [description]</description>
-- <source type=stype id= [sourceid]/>
-- <destination type=[accesstype1] id=“[accessid]”/>
-- <destination type=[accesstype2] id=" [accessid]2” />
-- <destination type="[accesstype3]" id="[accessid]3”/>
-- <category name="NEW" </category>
--</MAIN-file>

  Any help with this..

  Thanks a lot in advance..

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]