Create XML with parent and child element

• j.grimanis

  SSC Eights!

  Points: 852

  More actions

  July 8, 2015 at 4:23 pm

  #398718

  Hi,

  I want to create an XML file with the below format.

  office_tel and mobile are fields of a table.

  <fields>

  <office_tel>

  <value>1234</value>

  </office_tel>

  <mobile>

  <value>99999</value>

  </mobile>

  </fields>

  Thanks,

  John

• Eirikur Eiriksson

  SSC Guru

  Points: 182918

  More actions

  July 9, 2015 at 12:45 am

  #1811184

  j.grimanis (7/8/2015)

  ---

  Hi,

  I want to create an XML file with the below format.

  office_tel and mobile are fields of a table.

  <fields>

  <office_tel>

  <value>1234</value>

  </office_tel>

  <mobile>

  <value>99999</value>

  </mobile>

  </fields>

  Thanks,

  John

  Quick example using FOR XML PATH

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @SAMPLE TABLE (S_ID INT IDENTITY(1,1) NOT NULL,OFFICE_TEL VARCHAR(30) NOT NULL, MOBILE VARCHAR(30) NOT NULL);

  INSERT INTO @SAMPLE(OFFICE_TEL,MOBILE) VALUES

  ('01355740','091170514')

  ,('01620036','091170515')

  ,('01355741','091170516')

  ,('01355720','091170517')

  ,('01355727','091170518');

  SELECT

  S.OFFICE_TEL AS 'office_tel/value'

  ,S.MOBILE AS 'mobile/value'

  FROM @SAMPLE S

  FOR XML PATH('fields'), ROOT('root'), TYPE;

  Output

  <root>

  <fields>

  <office_tel>

  <value>01355740</value>

  </office_tel>

  <mobile>

  <value>091170514</value>

  </mobile>

  </fields>

  <fields>

  <office_tel>

  <value>01620036</value>

  </office_tel>

  <mobile>

  <value>091170515</value>

  </mobile>

  </fields>

  <fields>

  <office_tel>

  <value>01355741</value>

  </office_tel>

  <mobile>

  <value>091170516</value>

  </mobile>

  </fields>

  <fields>

  <office_tel>

  <value>01355720</value>

  </office_tel>

  <mobile>

  <value>091170517</value>

  </mobile>

  </fields>

  <fields>

  <office_tel>

  <value>01355727</value>

  </office_tel>

  <mobile>

  <value>091170518</value>

  </mobile>

  </fields>

  </root>

• j.grimanis

  SSC Eights!

  Points: 852

  More actions

  July 9, 2015 at 3:21 am

  #1811218

  thank you!