Ryan Cromwell

Someone was looking to do quick-and-dirty validation of an Xml document inside a SQL Server procedure today.  I assumed, and regretted, that he meant 2005, which would have looked like this:

declare @xml1 xml 
declare @xml2 xml 
set @xml1 = '<Node1><Node2 attrib="This is it." /></Node1>'
set @xml2 = '<Node1><Node2 /></Node1>'
select Col.value('count(./@attrib)', 'int') as count
from @xml1.nodes('Node1/Node2') as Tbl(Col)

select Col.value('count(./@attrib)', 'int') as count
from @xml2.nodes('Node1/Node2') as Tbl(Col)

After being told, "that blows up", I jogged the memory a bit and came up with this mess:

/* THE STUPID WAY */
DECLARE @hdoc int
DECLARE @doc1 varchar(MAX)
DECLARE @doc2 varchar(MAX)
set @doc1 = '<Node1><Node2 attrib="This is it." /></Node1>'
set @doc2 = '<Node1><Node2 /></Node1>'
/* DOC 1 */
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc1

IF EXISTS( SELECT    *
FROM       OPENXML (@hdoc, '/Node1/Node2/@attrib',1)
            WITH (attrib  varchar(20)))
BEGIN
PRINT 'Dumb ass'
END
exec sp_xml_removedocument @hdoc

/* DOC 2 */
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc2

IF EXISTS( SELECT    *
FROM       OPENXML (@hdoc, '/Node1/Node2/@attrib',1)
            WITH (attrib  varchar(20)))
BEGIN
PRINT 'Dumb ass'
END
exec sp_xml_removedocument @hdoc

 

Moral of the store?  UPGRADE!