Create XMl using for XML Path

• RamG

  Right there with Babe

  Points: 767

  More actions

  September 24, 2010 at 2:24 am

  #224303

  Please help me to create XML as below format using for XML Path.

  <Root>

  <R Id="1000000433">

  <Is>

  <ID="122334" DFmt="mm/dd/yyyy" ExpM="4545" N="" ExtD="09/17/2010" Id="1" />

  </Is>

  </R>

  </Root>

  I've tried the following query its not giving the expected result.

  SELECT

  '1000000433' "@ID",

  (select

  '122334' "ID",

  'mm/dd/yyyy' "DFmt",

  '4545' "ExpM",

  '' "N",

  '09/17/2010' "ExtD",

  '1' "Id"

  for xml path ('Is'), type)

  for XML Path ('R'), root ('Root')

  Regards,

  Ram.

• David McKinney

  SSChampion

  Points: 10472

  More actions

  September 24, 2010 at 2:34 am

  #1226767

  The xml you are trying to create isn't valid.

  You are on the right lines with your sql xml, but the problem is with your target.

  <Is>

  <ID="122334" DFmt="mm/dd/yyyy" ExpM="4545" N="" ExtD="09/17/2010" Id="1" />

  </Is>

  The above is not OK...the inner line does not have an element - only attributes. This isn't allowed.

  Regards,

  David.

• RamG

  Right there with Babe

  Points: 767

  More actions

  September 24, 2010 at 8:50 am

  #1227035

  Thanks David,

  Actually the XML file is

  <Is>

  <I D="122334" DFmt="mm/dd/yyyy" ExpM="4545" N="" ExtD="09/17/2010" Id="1" />

  </Is>

  Now i only i got the correct format. Thanks for your help again.:-)

  Regards,

  Ram