Post reply

export data to a xml with a XSD

  • November 3, 2009 at 6:33 am

    #139831

    Hi, I have a XSD file and data into a table. how can i export this data into a XML file with the XSD format?

    theres no SSIS export to xml files

    thanks

  • December 17, 2009 at 9:21 am

    #1093570

    Hello People!

    I want to know how I can get my resultset from my table to a XML-file with the structure of my XSD?

    Is it is possible with SSIS or in combination with Management Studio or .NET?

    I've already tried to use a "FOR XML" query in a variable in SSIS within the OleDBSource and then write it to a flatfile.

    THis works fine.

    ----------------------

    DECLARE @subq varchar(max)

    SET @subq = ''

    SET @subq = @subq + ( SELECT * FROM XXX FOR XML AUTO, ELEMENTS)

    SELECT @subq as XMLDATA

    ----------------------------

    But i can't figure out how to get it in the structure of the XSD.

    Can someone give me an small tutorial how to do this.

    Thanks in advance

  • December 17, 2009 at 9:40 am

    #1093577

    I don't know of a way to get SQL Server to generate the XML in a format that conforms to your XSD automatically. What you may need to do is load the XSD as an XML schema collection, create an XML datatype which points to the schema you loaded (IE - declare @SomeXML as XML(RecentlyLoadedSchema)), and then craft your select to build XML (using the sql xml tools of FOR XML .... etc). That way SQL server will let you know if the XML generated conforms to your schema.

    Although you may get a post from one of the experts telling you how you can do what you ask directly. Im just not sure of it myself.

  • August 6, 2010 at 3:50 am

    #1203333

    I have this same issue, did anyone ever come up with a best practises solution?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply