Здравствуйте, prike, Вы писали:

P>Можно ли в SQL Server 2000 хранить XML в колонке типа NTEXT (или TEXT — не суть важно) и обрабатывать его внутри хранимой процедуры. Т.е. ХП должна вытащить XML из таблицы, выдернуть из него XPath'ом какое-либо значение и на основе этого значения сделать выборку из другой таблицы.
P>Сама проблема собственно в том что xp_xml_preparedocument может принимать параметры типов char, nchar, varchar, nvarchar, text, или ntext, но что бы передать ntext туда его сначала надо вытащить из таблицы, а т.к. сохранить в локальной переменной NTEXT (как и другие blob данные нельзя) то такая операция не получается.
P>Подозреваю, что желанную обработку XML на стороне сервера реализовать не получится и прийдется все делать в .Net коде. Если это не так, подскажите плиз как лучше решить проблему.

Для чтения можно использовать "FOR XML".

А вот пример записи в BLOB поле на стороне сервера (T-SQL).

use tempdb
go

/*Create test table*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[emp]
go

CREATE TABLE [dbo].[emp](
    [ID] int,
    ENAME varchar(50),
    age int
)
go

/*fill test table with some values*/
insert into [dbo].[emp] values (1, 'judge', 28)
insert into [dbo].[emp] values (2, 'Cat2', 14)
insert into [dbo].[emp] values (3, 'akuz', 67)
insert into [dbo].[emp] values (4, 'hDrummer', 24)
GO

/*Procedure to display error for OLE object*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RaiseError]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[RaiseError]
GO

CREATE PROC [dbo].[RaiseError]
  @object int, -- OLE object token
  @user_description nvarchar(4000)=NULL -- User error message text 
AS
DECLARE @hr int, -- procedure return code
    @errormsg nvarchar(4000), -- error message text to display
    @source nvarchar(4000), -- OLE object error message source
    @description nvarchar(4000) -- OLE object error message description

-- try to get OLE object error 
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
  SET @errormsg = ''
  IF @user_description IS NOT NULL SET @errormsg = @user_description + char(13) + char(10)

  SET @errormsg = @errormsg + N'Source: ' + @source + char(13) + char(10)
  SET @errormsg = @errormsg + N'Description: ' + @description
  RAISERROR(@errormsg, 16, 1)
  RETURN
END
ELSE
BEGIN
  RAISERROR('Detailed error information can not be retrieved', 16, 1)
  RETURN
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PutXMLtoTEXT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[PutXMLtoTEXT]
GO

-- ***********************************************
-- PROCEDURE FOR PUTTING XML TO FIELD TEXT
-- ***********************************************
-- it is not possible to put the result of FOR XML to TEXT field
-- the trick is using COM that is the server acts  
-- as a COM client of itself to read the data of FOR XML 
CREATE PROC [dbo].[PutXMLtoTEXT] 
    @query varchar(8000), -- query text
    @dbname sysname = NULL -- database name
AS
DECLARE @sqlobject int, -- OLE object token for SQL Server
        @dbobject int, -- OLE object token for Database
        @hr int, -- procedure return value
        @results int, -- OLE object token for query results
    @row_count int -- query results row count

-- If database was not passed then using current database
IF @dbname IS NULL SET @dbname = DB_NAME()

-- create COM object SQLDMO.SQLServer
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sqlobject OUT
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Creating SQLDMO.SQLServer object'
  RETURN
END

-- Always using trusted connection
EXEC @hr = sp_OASetProperty @sqlobject, 'LoginSecure', 1
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Switch on trusted connection mode'
  RETURN
END

-- Switching off ODBC prefix
EXEC @hr = sp_OASetProperty @sqlobject, 'ODBCPrefix', 0
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Switching off ODBCPrefix'
  RETURN
END

-- Try to connect to SQLServer
EXEC @hr = sp_OAMethod @sqlobject, 'Connect', NULL, @@SERVERNAME
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Connecting to SQL Server'
  RETURN
END

-- Getting Database collection handler
EXEC @hr = sp_OAGetProperty @sqlobject, 'Databases', @dbobject OUT
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting Database collection handler'
  RETURN
END

-- Getting handler for choosen database
EXEC @hr = sp_OAMethod @dbobject, 'Item', @dbobject OUT, @dbname
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting handler for choosen database'
  RETURN
END

-- Executing query using ExecuteWithResults Method
EXEC @hr = sp_OAMethod @dbobject, 'ExecuteWithResults', @results OUT, @query
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject,  N'Executing query using ExecuteWithResults Method'
  RETURN
END

-- Getting query results row count
EXEC @hr = sp_OAMethod @results, 'Rows', @row_count OUT
IF @hr <> 0
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting query results row count'
  RETURN
END

DECLARE @ptrval binary(16),-- BLOB column handler
    @row int, -- current row number
    @out_text varchar(8000) -- current row value


-- Getting BLOB column handler
SELECT @ptrval = TEXTPTR(XML_DATA) FROM dbo.XML_EXPORT 
-- Starting with result 1st row
SET @row = 1

-- Cycle for all result rows
WHILE(@row <= @row_count)
BEGIN
    -- Retrivie next result row
    EXEC @hr = sp_OAMethod @results, 'GetColumnString', @out_text OUT, @row, 1
    IF @hr <> 0
    BEGIN
        EXEC dbo.RaiseError @sqlobject, N'Retrieving query data'
        RETURN
    END
    -- Adding row into our BLOB column
    UPDATETEXT XML_EXPORT.XML_DATA @ptrval Null 0 @out_text
    
    -- Choosing next row
    SET @row = @row + 1
END

-- Destroy OLE object
EXEC sp_OADestroy @sqlobject
IF @hr <> 0
BEGIN
    EXEC dbo.RaiseError @sqlobject, N'Destroying OLE object'
    RETURN
END
GO


-- now the example of putting the XML data to the TEXT field
/*It's our table to store results*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XML_EXPORT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[XML_EXPORT]
go

CREATE TABLE [dbo].[XML_EXPORT](
    [ID] int,
    [XML_DATA] text
)
go
-- We have to insert at least one record with empty value
insert into dbo.XML_EXPORT (XML_DATA) values ('');
-- By default query with FOR XML does not generate <root> tags
-- So we have to do this
update XML_EXPORT set XML_DATA = '<?xml version="1.0" encoding="windows-1252"?>'+char(13)+'<root>'+char(13) 

-- Call procedure to put xml into BLOB column
exec dbo.PutXMLtoTEXT 'select id, ename, age from emp for xml auto, elements'

-- And do not forget to add <root> tag at end
DECLARE @ptrval binary(16)
SELECT @PTRVAL = TEXTPTR(XML_DATA) FROM dbo.XML_EXPORT 
UPDATETEXT XML_EXPORT.XML_DATA @ptrval NULL 0 '</root>'

-- Checking results
select * from dbo.XML_EXPORT
go

Вот как можно прочитать потом XML из BLOB поля

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ParseXML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[ParseXML]
GO

-- ***********************************************
-- PROCEDURE FOR PARSING XML FROM FIELD TEXT
-- ***********************************************
-- you can declare a procedure with the paramenter TEXT
-- but you can not assign it a value within the database
-- you can only put it from the client
-- the workaround is to dynamically make a procedure call
-- by concatenating the chunks of varchars and execute it by EXEC
create proc ParseXML 
    @ParseProc as varchar(200) --
as
declare
  @datalen int,
  @sql varchar(8000),
  @sql1 varchar(8000),
  @sql2 varchar(8000),
  @sql3 varchar(8000),
  @sql4 varchar(8000),
  @sql5 varchar(8000),
  @sql6 varchar(8000),
  @sql7 varchar(8000),
  @cnt int
DECLARE @hDoc int

SELECT @datalen = DATALENGTH (xml_data) / 8000 + 1 FROM xml_export 

IF @datalen IS NULL RETURN

SET @cnt = 1
SET @sql = 'DECLARE' + CHAR(13)
WHILE (@cnt <= @datalen)
BEGIN
SELECT @SQL = @SQL + CASE @cnt WHEN 1 THEN '' ELSE ', ' + CHAR(13) END
              + ' @str' + CONVERT(varchar(10), @cnt) + ' varchar(8000)'
SET @cnt = @cnt + 1
END

SET @sql = @sql + ',' + CHAR( 13) + ' @textptr varbinary(16)' + CHAR(13) +
    'SELECT @textptr = TEXTPTR(xml_data) FROM xml_export' 

SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
    IF LEN(@SQL) < 7900
        SELECT @SQL = @SQL + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL1) < 7900
        SELECT @SQL1 = @SQL1 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL2) < 7900
        SELECT @SQL2 = @SQL2 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL3) < 7900
        SELECT @SQL3 = @SQL3 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) +
             ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL4) < 7900
        SELECT @SQL4 = @SQL4 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL5) < 7900
        SELECT @SQL5 = @SQL5 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) +
             ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE IF LEN(@SQL6) < 7900
        SELECT @SQL6 = @SQL6 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
    ELSE
        SELECT @SQL7 = @SQL7 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
            ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 

    SET @cnt = @cnt + 1
END

SET @SQL7 = @SQL7 + CHAR(13) + 'EXEC (''EXEC '+ @ParseProc + ' '''''' + '

SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
    SELECT @SQL7 = @SQL7 + CASE @cnt WHEN 1 THEN '' ELSE ' + ' END + '@str' + CONVERT(varchar(10), @cnt)
    SET @cnt = @cnt + 1
END
SET @SQL7 = @SQL7 + ' + '''''''')'

EXEC(@SQL+@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7)
GO

go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ParseEMP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[ParseEMP]
go

create proc [dbo].[ParseEMP] 
    @txt text as
DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @txt

 -----------------------------------------------------------------
 SELECT id, ename, age 
 FROM OPENXML(@hDoc, N'/root/emp') 
 with (id int 'id', ename varchar(30) 'ename', age int 'age') 
 -----------------------------------------------------------------

EXEC sp_xml_removedocument @hDoc
RETURN
GO

-- viewing the results
select XML_DATA from dbo.XML_EXPORT
-- now parse the XML data placed in TEXT field
exec dbo.ParseXML 'dbo.ParseEMP'

/* Results
id          ename                          age         
----------- ------------------------------ ----------- 
1           judge                          28
2           Cat2                           14
3           akuz                           67
4           hDrummer                       24

*/