Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Export File


XML Export File

Author
Message
sarwaanmca
sarwaanmca
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 193
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.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 7426
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
sarwaanmca
sarwaanmca
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 193
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
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2284 Visits: 7827
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
  • 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

  • hunchback
    hunchback
    SSC-Enthusiastic
    SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

    Group: General Forum Members
    Points: 117 Visits: 639
    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



    LutzM
    LutzM
    SSCertifiable
    SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

    Group: General Forum Members
    Points: 7011 Visits: 13559
    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
    hunchback
    hunchback
    SSC-Enthusiastic
    SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

    Group: General Forum Members
    Points: 117 Visits: 639
    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?



    LutzM
    LutzM
    SSCertifiable
    SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

    Group: General Forum Members
    Points: 7011 Visits: 13559
    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
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search