Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. SQL To XML

SQL To XML

Scheduled Pinned Locked Moved Database
csharpdatabaseasp-netcom
12 Posts 4 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • V Vimalsoft Pty Ltd

    Good Day All i have the Following Query that Procudes an XML

    DECLARE @CurrentTime DATETIME
    SET @CurrentTime = CURRENT_TIMESTAMP
    select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
    convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
    convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
    tu.name [Requested by]
    from tbl_booking tb inner join tbl_resource tr
    on tb.resources = tr.id
    inner join tbl_user tu on tu.id = tb.RequestedByUser
    where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
    order by [Room],[Start Time]
    FOR xml raw

    i want to Store the File as "XMLDoc1.XML" in my C:\ Kind Regards

    Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

    J Offline
    J Offline
    J4amieC
    wrote on last edited by
    #2

    Vuyiswa Maseko wrote:

    i want to Store the File as "XMLDoc1.XML" in my C:\

    And where is the problem?

    V 1 Reply Last reply
    0
    • J J4amieC

      Vuyiswa Maseko wrote:

      i want to Store the File as "XMLDoc1.XML" in my C:\

      And where is the problem?

      V Offline
      V Offline
      Vimalsoft Pty Ltd
      wrote on last edited by
      #3

      In Management Studio this will show the results, but i want to store this results in my C:\. i want to BCP. Thanks

      Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

      J W 2 Replies Last reply
      0
      • V Vimalsoft Pty Ltd

        In Management Studio this will show the results, but i want to store this results in my C:\. i want to BCP. Thanks

        Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

        J Offline
        J Offline
        J4amieC
        wrote on last edited by
        #4

        So whats the problem highlighting the XML in management studio right click>Save as...? Failing that, have a look at SqlCmd from a command prompt. It allows you to execute a query, and output the results to a file.

        V 1 Reply Last reply
        0
        • V Vimalsoft Pty Ltd

          In Management Studio this will show the results, but i want to store this results in my C:\. i want to BCP. Thanks

          Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

          W Offline
          W Offline
          wadhva_maninder
          wrote on last edited by
          #5

          You can use bcp to create xml in shared folder with Everyone rights and then can use FILE class to move to C:\ drive

          V 1 Reply Last reply
          0
          • V Vimalsoft Pty Ltd

            Good Day All i have the Following Query that Procudes an XML

            DECLARE @CurrentTime DATETIME
            SET @CurrentTime = CURRENT_TIMESTAMP
            select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
            convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
            convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
            tu.name [Requested by]
            from tbl_booking tb inner join tbl_resource tr
            on tb.resources = tr.id
            inner join tbl_user tu on tu.id = tb.RequestedByUser
            where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
            order by [Room],[Start Time]
            FOR xml raw

            i want to Store the File as "XMLDoc1.XML" in my C:\ Kind Regards

            Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

            A Offline
            A Offline
            Andy_L_J
            wrote on last edited by
            #6

            You can right_click in the results pane in SSMS and select save result as... Change the filename and extension and save.

            I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

            V 1 Reply Last reply
            0
            • A Andy_L_J

              You can right_click in the results pane in SSMS and select save result as... Change the filename and extension and save.

              I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

              V Offline
              V Offline
              Vimalsoft Pty Ltd
              wrote on last edited by
              #7

              i could get this in C# or doing a right click as you say, but i want the statement to create a File on the local drive

              Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

              J 1 Reply Last reply
              0
              • J J4amieC

                So whats the problem highlighting the XML in management studio right click>Save as...? Failing that, have a look at SqlCmd from a command prompt. It allows you to execute a query, and output the results to a file.

                V Offline
                V Offline
                Vimalsoft Pty Ltd
                wrote on last edited by
                #8

                i understand what you are Saying. But i want to do that from the above Query. not in C# nor SQL Management Studio

                Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                1 Reply Last reply
                0
                • W wadhva_maninder

                  You can use bcp to create xml in shared folder with Everyone rights and then can use FILE class to move to C:\ drive

                  V Offline
                  V Offline
                  Vimalsoft Pty Ltd
                  wrote on last edited by
                  #9

                  That is Good. Can you please Provide an Example based on the above query ?

                  Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                  1 Reply Last reply
                  0
                  • V Vimalsoft Pty Ltd

                    i could get this in C# or doing a right click as you say, but i want the statement to create a File on the local drive

                    Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                    J Offline
                    J Offline
                    J4amieC
                    wrote on last edited by
                    #10

                    SQL Statements are for extracting data from a database. As you know. IMO it is some client application which is responsible for doing somethign with that data, like displaying it, ort in your case, outputting it to a file. Im interested however, why you think you want to do this all with a SQL statement? Something (a process, an application, maybe SQL Agent) must be executing that statement....

                    V 1 Reply Last reply
                    0
                    • J J4amieC

                      SQL Statements are for extracting data from a database. As you know. IMO it is some client application which is responsible for doing somethign with that data, like displaying it, ort in your case, outputting it to a file. Im interested however, why you think you want to do this all with a SQL statement? Something (a process, an application, maybe SQL Agent) must be executing that statement....

                      V Offline
                      V Offline
                      Vimalsoft Pty Ltd
                      wrote on last edited by
                      #11

                      My client has a new Client that need to get this XML to be somewhere in the local drive. They are using SQl 2000 and am limited in a way. So with the above query i just need to save what is output in the local drive

                      Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                      J 1 Reply Last reply
                      0
                      • V Vimalsoft Pty Ltd

                        My client has a new Client that need to get this XML to be somewhere in the local drive. They are using SQl 2000 and am limited in a way. So with the above query i just need to save what is output in the local drive

                        Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                        J Offline
                        J Offline
                        J4amieC
                        wrote on last edited by
                        #12

                        Vuyiswa Maseko wrote:

                        My client has a new Client that need to get this XML to be somewhere in the local drive

                        So write youself a utility (Console App, Service, Windows App, VB Script) which executes your query and writes the result to the filesystem. Easy.

                        1 Reply Last reply
                        0
                        Reply
                        • Reply as topic
                        Log in to reply
                        • Oldest to Newest
                        • Newest to Oldest
                        • Most Votes

                        • Login
                        • Don't have an account? Register
                        • Login or register to search.
                        • First post
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • World
                        • Users
                        • Groups