how to insert xml declaration

• sam-433147

  Ten Centuries

  Points: 1221

  More actions

  October 22, 2015 at 4:46 am

  #100915

  hai,

  how can i insert the declaration

  <?xml version="1.0" encoding="UTF-8"?>

  to my query?

  declare @tbl table(Color varchar(20))

  insert into @tbl(Color)

  values ('Green')

  insert into @tbl(Color)

  values ('Red')

  select Color

  from @tbl Colors

  FOR XML AUTO, ROOT ('MyColors'), ELEMENTS XSINIL

  thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  October 22, 2015 at 5:04 am

  #1834855

  sam-433147 (10/22/2015)

  ---

  hai,

  how can i insert the declaration

  <?xml version="1.0" encoding="UTF-8"?>

  to my query?

  declare @tbl table(Color varchar(20))

  insert into @tbl(Color)

  values ('Green')

  insert into @tbl(Color)

  values ('Red')

  select Color

  from @tbl Colors

  FOR XML AUTO, ROOT ('MyColors'), ELEMENTS XSINIL

  thanks

  You cannot do this within a select for xml statement, you will have to manually add it to the output afterwards and obviously make certain that the encoding is actually UTF-8

  😎

• sam-433147

  Ten Centuries

  Points: 1221

  More actions

  October 22, 2015 at 5:42 am

  #1834861

  adding it manually is not an option

  is there an option to do it with modify or insert command?

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  October 22, 2015 at 7:35 am

  #1834888

  sam-433147 (10/22/2015)

  ---

  adding it manually is not an option

  is there an option to do it with modify or insert command?

  By manually, Eirikur may have meant that you will need to convert the result to a string and insert the declaration in the string.

  SQL Server does not allow you to include an xml declaration in an xml type.

  declare @tbl table(Color varchar(20))

  insert into @tbl(Color)

  values ('Green')

  insert into @tbl(Color)

  values ('Red')

  declare @result varchar(max);

  set @result = '<?xml version="1.0" encoding="UTF-8"?>' + cast((

  select Color

  from @tbl Colors

  FOR XML AUTO, ROOT ('MyColors'), ELEMENTS XSINIL, TYPE) AS varchar(max))

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]