xml parsing issue when xmlns present xquery sqlserver

Category: sql server xml

Question

Chakradhar Masineni on Tue, 15 Apr 2014 01:05:49


Hi ,

I have this xml

declare @xml xml 
set @xml ='<students xmlns="http://www.abcdef.com/pqrs/1.3.0" xsi:schemaLocation="http://www.abcdef.com/pqrs/1.3.0_f3">
	    <student>
		<id>1</id>
                <name>aaa</name>
            </student>
	</students>'


when I am xquering this data like below.

select 
	s.n.value('(id/text())[1]', 'varchar(50)') as id ,
	s.n.value('(name/text())[1]','varchar(50)') as name  
from 
	@XML.nodes('/students/student') as S(N)

Its not returning any rows. But when I remove the below lines in xml.

xmlns="http://www.abcdef.com/pqrs/1.3.0" xsi:schemaLocation="http://www.abcdef.com/pqrs/1.3.0_f3"

Its working fine. 

can any one tell me what is the reason ? how to over come this ?

Thanks in advance.

Replies

Russ Loski on Tue, 15 Apr 2014 16:47:56


Try:

declare @xml xml 
set @xml ='<students xmlns="http://www.abcdef.com/pqrs/1.3.0" xsi:schemaLocation="http://www.abcdef.com/pqrs/1.3.0_f3">
	    <student>
		<id>1</id>
                <name>aaa</name>
            </student>
	</students>';


; with xmlnamespaces (default 'http://www.abcdef.com/pqrs/1.3.0')
select 
	s.n.value('(id/text())[1]', 'varchar(50)') as id ,
	s.n.value('(name/text())[1]','varchar(50)') as name  
from 
	@XML.nodes('/students/student') as S(N)

I don't have my server running, but this should work.

The xmlns tells how to interpret the element names that don't have a prefix (n: or some such).  With XMLNamespaces tell xquery how to interpret the namespaces in your query.

This is my very short answer.

Elvis Long on Fri, 18 Apr 2014 06:28:23


Hello,

It seems contain incorrect "schemaLocation" in the definition, please refer to the following T-SQL below:

declare @xml xml 
set @xml ='<students xmlns="http://www.abcdef.com/pqrs/1.3.0">
	    <student>
		<id>1</id>
                <name>aaa</name>
            </student>
	</students>';


; with xmlnamespaces (default 'http://www.abcdef.com/pqrs/1.3.0')
select 
	s.n.value('(id/text())[1]', 'varchar(50)') as id ,
	s.n.value('(name/text())[1]','varchar(50)') as name  
from 
	@XML.nodes('/students/student') as S(N)

Regards,