• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Generating xml from a database

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Generating xml from a database

    Any good tools for generating xml getting data from a database?

    This is SQL Server. I am looking at using tsql commands.

    It is clearly doable - though sqlserver has some interesting considerations(duplicate rows etc.)

    SSIS seems to have nothing useful.

    #2
    Does the XML need to conform to an XSD schema?

    Or can it be any XML, as long as it's valid XML?

    Because if it's just any XML, simply put

    for xml auto

    on the end of any queries and then save the string.

    Comment


      #3
      Originally posted by DimPrawn View Post
      Does the XML need to conform to an XSD schema?

      Or can it be any XML, as long as it's valid XML?

      Because if it's just any XML, simply put

      for xml auto

      on the end of any queries and then save the string.
      Cheers for the response.

      It does need to correspond to a schema.

      Formatting the sql select to the xml string required is a huge pain. I thought there might be some tools out there to help - though maybe this is a niche requirement.

      Plan C maybe.....

      Comment


        #4
        XML to Database

        Any idea about tools or software to do the reverse transformation?
        Last edited by geoffreywhereveryoumaybe; 10 June 2016, 11:52. Reason: Typo

        Comment


          #5
          Originally posted by BrilloPad View Post
          Cheers for the response.

          It does need to correspond to a schema.

          Formatting the sql select to the xml string required is a huge pain. I thought there might be some tools out there to help - though maybe this is a niche requirement.

          Plan C maybe.....
          Yes, the T-SQL to force into into the required schema is excruitating....there are a few solutions, none of them I would call painless though.

          1. Export as raw XML from the SQL Server and then use C# to apply an XSLT tranform into the required XML schema.
          2. Read the data in C# using ADO.NET or EF or whatever and then generate the XML document from the data in C# code.
          3. Use SSIS to apply an XSLT

          Comment


            #6
            can you not structure the select in the query to concatenate the XML elements as strings together with the columns?

            Not pretty obviously.

            EDIT: sorry seen you probably mentioned that as being a pain.

            Comment


              #7
              Originally posted by BrilloPad View Post
              Any good tools for generating xml getting data from a database?

              This is SQL Server. I am looking at using tsql commands.

              It is clearly doable - though sqlserver has some interesting considerations(duplicate rows etc.)

              SSIS seems to have nothing useful.
              https://msdn.microsoft.com/en-GB/library/ms178107.aspx
              The greatest trick the devil ever pulled was convincing the world that he didn't exist

              Comment

              Working...
              X