XML Export File

• sarwaanmca

  Right there with Babe

  Points: 746

  More actions

  November 11, 2013 at 11:35 am

  #279014

  Hi Friends,

  The below i have mentioned the sample data and Table structure

  CREATE TABLE [dbo].[Xml_Export](

  [City] [nvarchar](200) NULL,

  [Company] [nvarchar](300) NOT NULL,

  [Bedrooms] [int] NULL,

  [Bathroom] [varchar](61) NULL,

  [Price] [varchar](8000) NULL,

  [Stock] [nvarchar](85) NOT NULL,

  [Phone] [nvarchar](50) NULL

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  INSERT [dbo].[Xml_Export] ([City], [Company], [Bedrooms], [Bathroom], [Price], [Stock], [Phone]) VALUES (N'chennai', N'RR Real Estate', 5, N'IV', N'$150000', N'FULL', N'9856354575')

  GO

  Using this data ..

  select * from Xml_Export listing for xml auto ,elements ,ROOT('Listings')

  i got the below result

  <Listings>

  <listing>

  <City>chennai</City>

  <Company>RR Real Estate</Company>

  <Bedrooms>5</Bedrooms>

  <Bathroom>IV</Bathroom>

  <Price>150000</Price>

  <Stock>FULL</Stock>

  <Phone>9856354575</Phone>

  </listing>

  </Listings>

  but my required result is

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

  <Listings>

  <listing>

  <City>chennai</City>

  <Company>RR Real Estate</Company>

  <Bedrooms>5</Bedrooms>

  <Bathroom>IV</Bathroom>

  <Price>150000</Price>

  <Stock>FULL</Stock>

  <Phone>9856354575</Phone>

  </listing>

  </Listings>

  How to get this Result.

  but it should be xml file formatted output..

  not like record set..

  For Example..

  DECLARE @Result XML

  select @Result= (select * from Xml_Export listing for xml auto ,elements ,ROOT('Listings'))

  SELECT '<?xml version="1.0" encoding="UTF-8" ?>'+CAST(@Result AS VARCHAR(MAX))

  Not like that above result..

  Thanks & Regards,

  K.D.Saravanan MCA

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  November 11, 2013 at 12:30 pm

  #1665554

  I think this will get you what you need:

  DECLARE @Result varchar(1000);

  WITH x(xx) AS (select * from Xml_Export listing for xml auto ,elements ,ROOT('Listings'))

  SELECT @Result='<?xml version="1.0" encoding="UTF-8" ?>'+xx

  FROM x

  --this will include the ?xml declaration

  --if you are exporting to an xml file then don't change it to xml

  SELECT @Result

  --This will strip the declaration

  SELECT CAST(@Result AS xml)

  I included two select statements in my example code to demonstrate how, when you cast as xml, the xml declaration is stripped out. For export you should declare @Result as varchar.

  Edit: Added comments to my code.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• sarwaanmca

  Right there with Babe

  Points: 746

  More actions

  November 11, 2013 at 12:48 pm

  #1665561

  Hi Friend,

  thanks for your support.

  but i already got this output..

  using the below simple single query..

  select * from Xml_Export listing for xml auto ,elements ,ROOT('Listings')

  but i need above result with below mentioned line that line should be first line of the output...

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

  and aslo out put should be like Above { XML hyperling } is must..

  Thanks & Regards,

  K.D.Saravanan MCA

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 11, 2013 at 4:44 pm

  #1665619

  How are you outputting the result ?

  The XML datatype in SQL Server does not include < ? xml ... descriptors because they are mainly useful for identifying that a text file contains XML and what encoding has been used.

  You will not get an xml file descriptor like that in a SQL Server xml data type, it is the job of whatever writes that XML to an external target (file, stream, whatever) to add the < ? xml ... > to the text.

  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]