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 = '
  xmlns="http://tempuri.org/Foo.xsd">
    -1
    InvalidFormat
    false
  
  xmlns="http://tempuri.org/Foo.xsd">
    -1
    MaxLength
    false
  
'

SELECT @Errors

DECLARE @x_handle int
exec sp_xml_preparedocument @x_handle OUTPUT, @Errors, ''

SELECT * 
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

0 comments: