Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Import XML file through a stored proc?

Options
  • 04-08-2004 11:32am
    #1
    Merrion
    Registered Users Posts: 1,421 ✭✭✭


    Does anyone know of any tool or whatnot through which I can import an XML file through a SQL Server 2000 stored proc.

    What I have is a file like:- 
    <?xml version="1.0" encoding="utf-8"?>
<PrinterMonitor Started="8/4/04 10:20:27">
  <PrinterInformationChanged>
    <PrinterName>\\DUBBDOM2\2_1stHP</PrinterName>
    <Location />
    <Comment />
    <Status PendingJobCount="0" Ready="true" DoorOpen="false" Error="false" 
Initialising="false" AwaitingManualFeed="false" OutOfToner="false" Unavailable="false" 
Offline="false" OutOfMemory="false" OutputBinFull="false" PaperJammed="false" 
OutOfPaper="false" Paused="false" DeletingJob="false" PowerSave="false" Printing="false" 
WaitingOnUserIntervention="false" WarmingUp="false" />
  </PrinterInformationChanged>
  <PrinterInformationChanged>
    <PrinterName>\\DUBBDOM2\2_1stHP</PrinterName>
    <Location />
    <Comment />
    <Status PendingJobCount="0" Ready="true" DoorOpen="false" Error="false" 
Initialising="false" AwaitingManualFeed="false" OutOfToner="false" Unavailable="false" 
Offline="false" OutOfMemory="false" OutputBinFull="false" PaperJammed="false" 
OutOfPaper="false" Paused="false" DeletingJob="false" PowerSave="false" Printing="false" 
WaitingOnUserIntervention="false" WarmingUp="false" />
  </PrinterInformationChanged>
</PrinterMonitor>

    And I want to call a stored proc "OnPrinterInformationChanged" once for each <PrinterInformationChanged> tag, filling the parameters according to what is in the values...

    Thanks in advance,
    Duncan


Comments

  • Options
    #2
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    Off the top of my head....

    Look at the DTS (Data Transformation Services) in MSSQL 2000. These should have the capability to import the data to your destination table, with any amount of jiggery-pokery you want.

    Running it from a stored proc may be a bit messy, but it can be done. I think you may have to shell to the command line to do it though.

    jc


  • Options
    #3
    YellowMan
    Registered Users Posts: 9 ✭YellowMan


    Hi !
    May be something like this can help - you will get all fields from the XML and then do whatever you want to do.


    DECLARE @XML VARCHAR(5000)
    SET @XML='
    <?xml version="1.0" encoding="utf-8"?>
    <PrinterMonitor Started="8/4/04 10:20:27">
    <PrinterInformationChanged>
    <PrinterName>\\DUBBDOM2\2_1stHP</PrinterName>
    <Location />
    <Comment />
    <Status PendingJobCount="0" Ready="true" DoorOpen="false" Error="false"
    Initialising="false" AwaitingManualFeed="false" OutOfToner="false" Unavailable="false"
    Offline="false" OutOfMemory="false" OutputBinFull="false" PaperJammed="false"
    OutOfPaper="false" Paused="false" DeletingJob="false" PowerSave="false" Printing="false"
    WaitingOnUserIntervention="false" WarmingUp="false" />
    </PrinterInformationChanged>
    <PrinterInformationChanged>
    <PrinterName>\\DUBBDOM2\2_1stHP</PrinterName>
    <Location />
    <Comment />
    <Status PendingJobCount="0" Ready="true" DoorOpen="false" Error="false"
    Initialising="false" AwaitingManualFeed="false" OutOfToner="false" Unavailable="false"
    Offline="false" OutOfMemory="false" OutputBinFull="false" PaperJammed="false"
    OutOfPaper="false" Paused="false" DeletingJob="false" PowerSave="false" Printing="false"
    WaitingOnUserIntervention="false" WarmingUp="false" />
    </PrinterInformationChanged>
    </PrinterMonitor>
    '
    DECLARE @hDoc int
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


    SELECT PendingJobCount,Ready--,ETC,ETC,ETC
    FROM
    OPENXML(@hDoc, N'/PrinterMonitor/PrinterInformationChanged/Status')
    WITH (PendingJobCount INT,Ready VARCHAR(20))--ETC,ETC,ETC

    EXEC sp_xml_removedocument @hDoc


    PS Sorry for the formatting - my first message here...


Advertisement