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="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Error xmlns="http://tempuri.org/Foo.xsd"> <Code>-1</Code> <UniqueName>InvalidFormat</UniqueName> <Warning>false</Warning> </Error> <Error xmlns="http://tempuri.org/Foo.xsd"> <Code>-1</Code> <UniqueName>MaxLength</UniqueName> <Warning>false</Warning> </Error> </Errors>' SELECT @Errors DECLARE @x_handle int exec sp_xml_preparedocument @x_handle OUTPUT, @Errors, '<root xmlns:x="http://tempuri.org/Foo.xsd"/>' 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
1 comment:
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 =
'
maca
twix
'
declare @h int
execute sp_xml_preparedocument @h output, @q, ''
select
*
from openxml(@h, '//comida/e:doce/s:saudavel', 2)
with([g:exemplo] varchar(max))
execute sp_xml_removedocument @h
Post a Comment