Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Export File Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 11:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
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
Post #1513208
Posted Monday, November 11, 2013 12:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1513224
Posted Monday, November 11, 2013 12:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
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
Post #1513233
Posted Monday, November 11, 2013 4:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 1,660, Visits: 5,223
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1513302
    Posted Tuesday, November 12, 2013 11:48 AM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Yesterday @ 1:47 PM
    Points: 99, Visits: 499
    Have the descriptor in a separated file and do the concatenation at the command side using COPY.

    c:\>copy f1 /B + f2 /B f3 /B




    Post #1513578
    Posted Tuesday, November 12, 2013 12:35 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:20 PM
    Points: 6,957, Visits: 12,716
    hunchback (11/12/2013)
    Have the descriptor in a separated file and do the concatenation at the command side using COPY.

    c:\>copy f1 /B + f2 /B f3 /B



    Why would someone use the file system just to add a separate row?

    If the output is finally stored in a file, why not using the VARCHAR() data type inside SQL Server?
    If the output is used by a different application, then this app should "convert" the output to xml (which is an implicit conversion).

    There's just not enough information provided so far why it has to be XML format within SQL Server...

    Btw: If you add the encoding outside SQL Server and re-import the file into an xml variable, the encoding is gone again...




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1513597
    Posted Tuesday, November 12, 2013 4:22 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Yesterday @ 1:47 PM
    Points: 99, Visits: 499
    Because the requirement is to have an xml file, not a caracter text one, with the prolog / encoding / descriptor or whatever it is. SQL Server does not allow us to do it as of today.

    Question: why not to use the fs to accomplish it?



    Post #1513658
    Posted Tuesday, November 12, 2013 4:41 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:20 PM
    Points: 6,957, Visits: 12,716
    hunchback (11/12/2013)
    Because the requirement is to have an xml file, not a caracter text one, with the prolog / encoding / descriptor or whatever it is. SQL Server does not allow us to do it as of today.

    Question: why not to use the fs to accomplish it?


    I'm not sure what the difference between an xml file and a character text file is from your perspective...

    If you'd use Alans approach (equal to the one you mentioned in your very first post) and save the result set with an xml extension instead of a txt extension, what would be wrong with it?

    From my point of view a xml file is nothing else but a character text file with a specific structure and a dedicated file extension. So, if the content of the file is valid (as it would be with Alans approach) and the file extension is correct (which is easy to accomplish) then I don't see any reason why this file would not getting processed as a xml file.

    There's still a good chance for me being totally off track here. But I'd like to see an actual example from your side where you can't get a xml file using Alans approach (table def, sample data, current code for the complete task and expected result - including file name).

    It seems like the missing link is what you define as "xml file formatted output". Is this the content of a file physically stored on the drive? Or any "representation" of a "Wannabe-file"?

    Please note that we neither have all the information you have nor can we look over your shoulder (except for the folks working for NSA...). All we have ist what you've posted so far. And, at least to me, it's not a clear picture. Yet.




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1513659
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse