Import XML file through a stored proc?

Merrion

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

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

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...