Здравствуйте, 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
