(Activator.CreateInstance(Type.GetTypeFromProgID("System.Management"))

-- Create a table to receive WMI information
BEGIN TRAN
	IF OBJECT_ID(N'[DBO].[WMIDATATEST]') IS NOT NULL
	DROP TABLE DBO.WMIDATATEST;

	CREATE TABLE Dbo.WmiDataTest (
						ID			INT	IDENTITY(1,1) PRIMARY KEY NOT NULL,
						Class		VARCHAR(200) NOT NULL,
						Property	VARCHAR(200) NOT NULL,
						VarType		VARCHAR(200) NOT NULL,
						Value		VARCHAR(200)     NULL
	);
	GRANT SELECT ON dbo.WmiDataTest TO webreport_approle;
COMMIT TRAN
go
SET NOCOUNT ON;

DECLARE @CMD      VARCHAR(8000);
DECLARE @X        XML;
DECLARE @IDOC     INT;
DECLARE @Class    VARCHAR(20);
DECLARE @Ret      INT;
DECLARE @RetTable TABLE ([Output] nvarchar(255) );
BEGIN TRY
	SET @Class = 'Printer'

	--generate the XML using WMIC.EXE and write it into a file
	-- after I gave write permission on the output folder to SQL, this worked.
	set @CMD = 'WMIC ' + @Class + ' get /translate:basicxml /format:rawxml.xsl ' 
			 + '> c:\users\Shaun\documents\outputs\win32_printer_instances.xml'
    INSERT INTO @RetTable EXECUTE @Ret = XP_CMDSHELL @CMD;
	IF (@RET = 0)
	BEGIN
		--READ THE XML INTO A VARIABLE
		SELECT @X = CAST(BULKCOLUMN AS XML) FROM OPENROWSET ( 
		BULK 'c:\users\Shaun\documents\outputs\win32_printer_instances.xml' ,SINGLE_BLOB 
		) AS XMLDATA;

		--PARSE THE DESIRED XML VALUES IN THE VARIABLE AND INSERT THEM INTO THE TABLE
		EXEC sp_xml_PrepareDocument @idoc OUTPUT, @X;

		INSERT INTO dbo.WmiDataTest (Class, property, vartype, value)
		SELECT   Class, Property, VarType, CASE LTRIM(RTRIM(Value)) 
										   WHEN 'NULL' THEN NULL 
										   ELSE Value 
										   END
		FROM       OPENXML (@idoc, '//PROPERTY')
		WITH (
		  Class    VARCHAR(200) './@CLASSORIGIN'
		, Property VARCHAR(200) './@NAME'
		, VarType  VARCHAR(200) './@TYPE'
		, Value    VARCHAR(200) './VALUE'
		) ;
		EXEC sp_xml_RemoveDocument @idoc;
	END
	ELSE	   
	BEGIN
		DECLARE @Message VARCHAR(255);
		SELECT TOP 1 @Message = [Output] FROM @RetTable;
		RAISERROR ( 'WMIC.EXE failed with this message: %s', 16, 1, @Message );
		-- Wmic.exe can only be used by the local system administrators 
		-- regardless of WMI namespace permissions on the local machine. 
	END;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage	NVARCHAR(4000);
    DECLARE @ErrorSeverity	INT;
    DECLARE @ErrorState		INT;
    SELECT @ErrorMessage	= ERROR_MESSAGE(),
           @ErrorSeverity	= ERROR_SEVERITY(),
           @ErrorState		= ERROR_STATE();
    RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH;

SELECT * FROM dbo.WmiDataTest;