Можно ли в SQL Server 2000 хранить XML в колонке типа NTEXT (или TEXT — не суть важно) и обрабатывать его внутри хранимой процедуры. Т.е. ХП должна вытащить XML из таблицы, выдернуть из него XPath'ом какое-либо значение и на основе этого значения сделать выборку из другой таблицы.
Сама проблема собственно в том что xp_xml_preparedocument может принимать параметры типов char, nchar, varchar, nvarchar, text, или ntext, но что бы передать ntext туда его сначала надо вытащить из таблицы, а т.к. сохранить в локальной переменной NTEXT (как и другие blob данные нельзя) то такая операция не получается.
Подозреваю, что желанную обработку XML на стороне сервера реализовать не получится и прийдется все делать в .Net коде. Если это не так, подскажите плиз как лучше решить проблему.
Здравствуйте, 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
*/
... << RSDN@Home 1.2.0 alpha rev. 746>>
Здравствуйте, kuj, Вы писали:
kuj>Вот как можно прочитать потом XML из BLOB поля
kuj> ... (код вырезан)
Спасибо за ответ, он мне очень помог в реализации и поиске информации.
Резюмируя информацию доступную по данной теме, хочу сказать следующее (относится к SQL Server 2000):
1) Предложенная kuj идея обработки является пожалуй единственным доступным workarround'ом. Основной смысл заключается в использовании особенности оператора EXEC которые позволяет выполнять любый SQL выражения, произвольной длинны. Т.о. строится динамический SQL который выполняет обработку текста произвольной длины, состоящей из блоков считанных из BLOB поля.
Пример использования данного подхода доступен здесь:
http://www.sql.ru/faq/faq_topic.aspx?fid=398
(В указанной статье показаны теже самые примеры SQL кода, что были опубликованы kuj, плюс добавлены краткие комментарии)
В данном примере присутвует одна ошибка из-за которой он в реальности работать не будет, т.к. используется двойной динамический SQL, то символы апострофа в тексте прочитанном из BLOB поля должны быть удвоены. Код удвоения может выглядеть следующим образом REPLACE(str, CHAR(39), CHAR(39) + CHAR(39)) (естественно данный код входит в состав динамического SQL)
2) Кроме того в упомянутом выше примере есть основной недостаток — в данном подходе используется двойной динамический SQL, в начале формируется текст динамического SQL, в котором объявляются переменные, далее поблочно считывается значение BLOB поля в данные переменные, затем используется еще один вложенный динамический SQL для выполнения хранимой процедуры, в качестве параметра которой передается результат конкатенации переменных (для хранения текста динамического SQL требуется приблизительно столько же переменных максимальной длины, сколько объявляется в динамическом @SQL для чтения участков BLOB поля). Если имеется возможность неиспользовать двойной динамический запрос ИМХО (
объективные возражения принимаются) лучше выполнить чтение участков BLOB поля непосредственно в переменные хранимой процедуры. Возможные преимущества этого в том, что не требуется повторного выделения памяти для переменных (см. п. 5), хранящих текст BLOB, как в случае с двойным динамическим SQL.
(Также следует отметить что для данного подхода удвоение символа апострофа не требуется, т.к. здесь используется только одноуровневый динамический SQL)
Пример использования данного подхода и его обсуждение доспуно по следующему адресу:
http://sqlxml.org/faqs.aspx?faq=42
3) На основе п. 1 и 2 можно сказать что подход с двойным динамическим SQL следует использовать, только тогда когда необходима универсальная процедура позволяющая обрабатывать BLOB поле по произвольному запросу передаваемому ей как текст. (Пример такой процедуры показан ниже в п. 8.)
4) Кроме того, можно рассмотреть подход написания хранимой процедуры открывающей XML документ, хранимый в BLOB поле, процедурой sp_xml_preparedocument и возвращающей хендл открытого документ, это иногда бывает удобнее чем передача имени хранимой процедуры обрабатывающей XML (как в варианте предложенном kuj), например когда необходимо использовать OUTPUT параметры и не хочется думать об контексте в котором выполняется динамический SQL
. Пример такого подхода расположен по следующему адресу
http://sqlxml.org/faqs.aspx?faq=42, который уже упоминался в п.2. (Кроме того в п. 8. опубликован пример использования данного подхода.)
5) Также хотел отметить что оператор EXEC обладает одной интересной особенность, как известно он допускает склеивание строковых констант или переменных, например EXEC(N'SELECT * ' + @SQL1 + @SQ2 + ...+ @SQLN), длинна строки которую в конечном итоге обрабатывает оператор EXEC ограничивается только доступной памятью сервера. Кроме того при выполнении конкатенации строковых констант или переменных, сервер не выделяет память для результата конкатенации а выполняет конкатенацию "логически" при выполнении выражения.
6) Хоть это и видно из примеров, но это стоит подчеркнуть еще раз, любой из указанных подходов позволяет обрабатывать BLOB данные, длина которых не превышает определенного значения (обычно приблизительно равного количеству переменных используемых для хранения текста двойного динамического SQL или точно равного количеству переменных используемых для хранания частей BLOB данных в подходе с одиночным динамическим SQL помноженного на длину каждой из переменных). Т.о. если требуется обрабатывать более длинные BLOB данные необходимо увеличить количество используемых переменных.
Кроме того существует и второй недостаток, при обработке XML используется DOM модель, весь документ загружается в память, поэтому в случае очень больших XML документов возможно следует подумать над альтернативными подходами.
7) Пища к размышлению: когда Вас не устраивает ни один из предложенных выше подходов для обработки XML в качестве альтернативных можно предложить:
рассмотреть XML как простой текст и использовать строковые функции для его обработки PATINDEX, REPLACE и т.д.
обрабатывать XML вне SQL сервер (на клиенте или реализовать и использовать расширенные хранимые процедуры)
8) Ниже обещанный пример хранимой процедуры, которую я написал для решения своей задачи
CREATE PROCEDURE dbo.PrepareXML
@hDoc INT OUTPUT, -- Handle of XML document that should be opened
@XPathNamespaces NVARCHAR(4000), -- Namespace declarations that are used in row and column XPath expressions in OPENXML
@SelectQuery NVARCHAR(500) -- Query to select TEXTPTR for field specified by @FieldName
AS
BEGIN
-- you can declare a procedure with the parameter TEXT or NTEXT
-- 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
-- as EXEC can execute arbitrary long string (maximum length is resticted only by
-- the server's available memory)
DECLARE
@Datalen int,
@SQL nvarchar(4000),
@SQL1 nvarchar(4000),
@SQL2 nvarchar(4000),
@SQL3 nvarchar(4000),
@SQL4 nvarchar(4000),
@SQL5 nvarchar(4000),
@SQL6 nvarchar(4000),
@SQL7 nvarchar(4000),
@SQL8 nvarchar(4000),
@SQL9 nvarchar(4000),
@SQL10 nvarchar(4000),
@SQL11 nvarchar(4000),
@SQL12 nvarchar(4000),
@SQL13 nvarchar(4000),
@SQL14 nvarchar(4000),
@Count int
DECLARE @DataLenQuery NVARCHAR(1024)
DECLARE @FieldName SHORT_DESC
SET @DataLenQuery = REPLACE(REPLACE(@SelectQuery, N'TEXTPTR', N'@Datalen = DATALENGTH'), N')', N') / 4000 + 1')
EXEC sp_executesql @DataLenQuery, N'@Datalen INT OUTPUT', @Datalen = @Datalen OUT
IF @Datalen IS NULL RETURN
IF @Datalen > 14 BEGIN
RAISERROR('The XML to process is too long. Max value length should not be greater than 120,000 bytes.', 16, 1)
RETURN 1
END
SET @Count = 1
SET @SQL = 'DECLARE' + NCHAR(13)
WHILE (@Count <= @Datalen)
BEGIN
SELECT @SQL = @SQL + CASE @Count WHEN 1 THEN '' ELSE ', ' + NCHAR(13) END
+ ' @str' + CONVERT(nvarchar(10), @Count) + ' NVARCHAR(4000)'
SET @Count = @Count + 1
END
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @SQL8 = ''
SET @SQL9 = ''
SET @SQL10 = ''
SET @SQL11 = ''
SET @SQL12 = ''
SET @SQL13 = ''
SET @SQL14 = ''
SET @Count = 1
WHILE (@Count <= @Datalen)
BEGIN
-- Read all chunks of XML and replace ' with ''
IF LEN(@SQL) < 3900
SELECT @SQL = @SQL + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL1) < 3900
SELECT @SQL1 = @SQL1 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL2) < 3900
SELECT @SQL2 = @SQL2 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL3) < 3900
SELECT @SQL3 = @SQL3 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL4) < 3900
SELECT @SQL4 = @SQL4 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL5) < 3900
SELECT @SQL5 = @SQL5 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL6) < 3900
SELECT @SQL6 = @SQL6 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL7) < 3900
SELECT @SQL7 = @SQL7 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL8) < 3900
SELECT @SQL8 = @SQL8 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL9) < 3900
SELECT @SQL9 = @SQL9 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL10) < 3900
SELECT @SQL10 = @SQL10 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL11) < 3900
SELECT @SQL11 = @SQL11 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL12) < 3900
SELECT @SQL12 = @SQL12 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE IF LEN(@SQL13) < 3900
SELECT @SQL13 = @SQL13 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
ELSE
SELECT @SQL14 = @SQL14 + NCHAR(13) + REPLACE(REPLACE(@SelectQuery,
N'TEXTPTR',
N' @str' + CONVERT(nvarchar(10), @Count) + N' =REPLACE(SUBSTRING'),
N')',
N', ' + CONVERT (nvarchar(30),(@Count-1) * 4000 + 1) + N', 4000), CHAR(39), CHAR(39) + CHAR(39))'
)
SET @Count = @Count + 1
END
SET @SQL14 = @SQL14 + NCHAR(13) +
N'EXEC ('' DECLARE @hDoc INT, @ErrCode INT ' +
N'EXEC @ErrCode = sp_xml_preparedocument @hDoc OUT, N'''''' + '
SET @Count = 1
WHILE (@Count <= @Datalen)
BEGIN
SELECT @SQL14 = @SQL14 + CASE @Count WHEN 1 THEN '' ELSE ' + ' END + '@str' + CONVERT(Nvarchar(10), @Count)
SET @Count = @Count + 1
END
SET @SQL14 = @SQL14 + ' + '''''', N''''' + @XPathNamespaces + N''''' ' +
N'DECLARE he_cur CURSOR FOR SELECT @hDoc, @ErrCode '') '
DECLARE @ErrCode INT
EXEC(@SQL+@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7+@SQL8+@SQL9+@SQL10+@SQL11+@SQL12+@SQL13+@SQL14)
OPEN he_cur
FETCH he_cur INTO @hDoc, @ErrCode
DEALLOCATE he_cur
RETURN @ErrCode
END
GO
--------------------------------------------------
-- Example of use
--------------------------------------------------
DECLARE @hDoc INT
EXEC dbo.PrepareXML
@hDoc = @hDoc OUTPUT,
@XPathNamespaces = N'<root xmlns:smth="http://www.something.com/bla/bla"/>', -- NULL can be here if you don't need to use namespaces aliases
@SelectQuery = N'SELECT TEXTPTR(XML) FROM T_XML_TABLE WHERE ID = 1' -- TEXTPTR is actually just a marker that shows to PrepareXML column that contains XML
-- Select some data
SELECT * FROM OPENXML(@hDoc, N'//Element')
-- Don't forget to free handle
EXEC sp_xml_removedocument @hDoc
В данной реализации запрос который принимает PrepareXML должен содержать функцию TEXTPTR, на самом деле здесь могло быть любое сочетание символов вместо TEXTPTR, это всего лишь некий маркер который позволяет процедуре PrepareXML разобраться где в тексте расположен идентификтор колонки с XML.
Источники информации по теме:
1) SQL Server Books Online
2) Статья Joseph Gama посвященная работе с BLOB данными, описанные выше подходы основаны на идеях данной статьи
http://msdn2.microsoft.com/en-us/library/Aa175795(SQL.80).aspx
3)
http://sqlxml.org/faqs.aspx?faq=42
4)
http://www.sql.ru/faq/faq_topic.aspx?fid=398
