Thursday, April 05, 2007

OPENXML and sp_xml_preparedocument with Namespaces

Posting this for mainly my reference, as the SQL Server documentation is a bit slim. When your XML has namespaces, sp_xml_preparedocument and OPENXML have to be used differently. The bold text below highlights the gotchas.

DECLARE @Errors nvarchar(2000)
SET @Errors = '<Errors xmlns:xsi="" xmlns:xsd="">
  <Error xmlns="">
  <Error xmlns="">

SELECT @Errors

DECLARE @x_handle int
exec sp_xml_preparedocument @x_handle OUTPUT, @Errors, '<root xmlns:x=""/>'

FROM OPENXML(@x_handle, '/Errors/x:Error', 2)
 WITH (UniqueName nvarchar(50) 'x:UniqueName', Warning nvarchar(5) 'x:Warning') XmlErrors

exec sp_xml_removedocument @x_handle

1 comment:

Anonymous said...

what if you have 2 namespaces in your xml?

well then all you need to do is declare these namespaces next to each other , separated by a space

declare @q xml =




declare @h int
execute sp_xml_preparedocument @h output, @q, ''

from openxml(@h, '//comida/e:doce/s:saudavel', 2)
with([g:exemplo] varchar(max))

execute sp_xml_removedocument @h