Add a comment for XML output from SQL using "FOR XML EXPLICIT"

• mmi 9526

  SSC Enthusiast

  Points: 155

  More actions

  December 8, 2011 at 9:41 am

  #391075

  Input SQL Query

  declare @agent table

  (

  AgentID int,

  Fname varchar(5),

  SSN varchar(11)

  )

  insert into @agent

  select 1, 'Vimal', '123-23-4521' union all

  select 2, 'Jacob', '321-52-4562' union all

  select 3, 'Tom', '252-52-4563'

  declare @address table

  (

  AddressID int,

  AddressType varchar(12),

  Address1 varchar(20),

  Address2 varchar(20),

  City varchar(25),

  AgentID int

  )

  insert into @address

  select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all

  select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all

  select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all

  select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all

  select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all

  select 6, 'Home', 'ttt', 'loik road', 'NY', 3

  SELECT

  1 AS Tag,

  NULL AS Parent,

  0 AS 'Agents!1!Sort!hide',

  NULL AS 'Agents!1!',

  NULL AS 'Agent!2!AgentID',

  NULL AS 'Agent!2!Fname!Element',

  NULL AS 'Agent!2!SSN!Element',

  NULL AS 'AddressCollection!3!Element',

  NULL AS 'Address!4!AddressType!Element',

  NULL AS 'Address!4!Address1!Element',

  NULL AS 'Address!4!Address2!Element',

  NULL AS 'Address!4!City!Element'

  UNION ALL

  SELECT

  2 AS Tag,

  1 AS Parent,

  AgentID * 100,

  NULL, AgentID, Fname, SSN,

  NULL,NULL, NULL, NULL, NULL

  FROM @Agent

  UNION ALL

  SELECT

  3 AS Tag,

  2 AS Parent,

  AgentID * 100 + 1,

  NULL,NULL,NULL, NULL,

  NULL, NULL, NULL, NULL, NULL

  FROM @Agent

  UNION ALL

  SELECT

  4 AS Tag,

  3 AS Parent,

  AgentID * 100 + 2,

  NULL,NULL,NULL,NULL,NULL,

  AddressType, Address1, Address2, City

  FROM @Address

  ORDER BY [Agents!1!Sort!hide]

  FOR XML EXPLICIT

  Output:

  <Agents>

  <Agent AgentID="1">

  <Fname>Vimal</Fname>

  <SSN>123-23-4521</SSN>

  <AddressCollection>

  <Address>

  <AddressType>Home</AddressType>

  <Address1>abc</Address1>

  <Address2>xyz road</Address2>

  <City>RJ</City>

  </Address>

  <Address>

  <AddressType>Office</AddressType>

  <Address1>temp</Address1>

  <Address2>ppp road</Address2>

  <City>RJ</City>

  </Address>

  </AddressCollection>

  </Agent>

  <Agent AgentID="2">

  <Fname>Jacob</Fname>

  <SSN>321-52-4562</SSN>

  <AddressCollection>

  <Address>

  <AddressType>Home</AddressType>

  <Address1>xxx</Address1>

  <Address2>aaa road</Address2>

  <City>NY</City>

  </Address>

  <Address>

  <AddressType>Office</AddressType>

  <Address1>ccc</Address1>

  <Address2>oli Com</Address2>

  <City>CL</City>

  </Address>

  <Address>

  <AddressType>Temp</AddressType>

  <Address1>eee</Address1>

  <Address2>olkiu road</Address2>

  <City>CL</City>

  </Address>

  </AddressCollection>

  </Agent>

  <Agent AgentID="3">

  <Fname>Tom</Fname>

  <SSN>252-52-4563</SSN>

  <AddressCollection>

  <Address>

  <AddressType>Home</AddressType>

  <Address1>ttt</Address1>

  <Address2>loik road</Address2>

  <City>NY</City>

  </Address>

  </AddressCollection>

  </Agent>

  </Agents>

  Expected Output (Add a Comment):

  <Agent AgentID="1">

  <Fname>Vimal</Fname>

  <SSN>123-23-4521</SSN>

  <AddressCollection>

  <Address>

  <!-- Home Address -->

  <AddressType>Home</AddressType>

  <Address1>abc</Address1>

  <Address2>xyz road</Address2>

  <City>RJ</City>

  </Address>

  <Address>

  <!-- Office Address -->

  <AddressType>Office</AddressType>

  <Address1>temp</Address1>

  <Address2>ppp road</Address2>

  <City>RJ</City>

  </Address>

  </AddressCollection>

  </Agent>

  <Agent AgentID="2">

  <Fname>Jacob</Fname>

  <SSN>321-52-4562</SSN>

  .

  .

  .

  .

  .

  .

  and so on

  Please Help !!!!!!

• mmi 9526

  SSC Enthusiast

  Points: 155

  More actions

  December 8, 2011 at 1:35 pm

  #1418951

  Hi Is there anyone work with SQL XML before to help me. Please help me if you can. I had tried all the options I could but no luck.