sql server xpath query with namespace nodes does not return result

Question

Greetings...

I am using a sql server query with xmlnamespaces but does not return results at all.

Output:

Tbl_Name	Col_Name
customer	ID
customer	Code
customer	Name

Query:

DECLARE @xml xml
SET @xml = 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.myweb.report/customer" xmlns:nyw="urn:com.netyourwork/aod" elementFormDefault="qualified" attributeFormDefault="qualified" targetNamespace="urn:com.myweb.report/customer">
<xsd:element name="customer" type="wd:DataType"/>
<xsd:simpleType name="RichText">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:complexType name="Type">
<xsd:sequence>
<xsd:element name="ID" type="xsd:string"/>
<xsd:element name="Code" type="xsd:string"/>
<xsd:element name="Name" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="DataType">
<xsd:sequence>
<xsd:element name="Entry" type="wd:Type" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>'		

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xsd, 'urn:com.myweb.report/customer' as wd, 'urn:com.netyourwork/aod' as nyw)

select t.t.value('@name', 'nvarchar(256)') as tbl,
	   c.c.value('@name', 'nvarchar(256)') as col
  from @xml.nodes('xsd:schema/xsd:element') t(t) 
 cross apply t.t.nodes('.//xsd:element[not(.//xsd:element)]') as c(c)

Answer 1

Hi @Raj D,

Please try the following solution.

It is easier to use a DEFAULT namespace.

DECLARE @xml xml;
SET @xml = 
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wd="urn:com.myweb.report/customer" xmlns:nyw="urn:com.netyourwork/aod" elementFormDefault="qualified" attributeFormDefault="qualified" targetNamespace="urn:com.myweb.report/customer">
	<xsd:element name="customer" type="wd:DataType"/>
	<xsd:simpleType name="RichText">
		<xsd:restriction base="xsd:string"/>
	</xsd:simpleType>
	<xsd:complexType name="Type">
		<xsd:sequence>
			<xsd:element name="ID" type="xsd:string"/>
			<xsd:element name="Code" type="xsd:string"/>
			<xsd:element name="Name" type="xsd:string"/>
		</xsd:sequence>
	</xsd:complexType>
	<xsd:complexType name="DataType">
		<xsd:sequence>
			<xsd:element name="Entry" type="wd:Type" minOccurs="0" maxOccurs="unbounded"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>';

;WITH XMLNAMESPACES (DEFAULT 'http://www.w3.org/2001/XMLSchema')
select p.value('(element/@name)[1]', 'SYSNAME') as tbl
	, c.value('@name', 'SYSNAME') as col
from @xml.nodes('/schema') t1(p) 
	CROSS APPLY p.nodes('complexType[@name="Type"]/sequence/element') as t2(c);

Output

tbl	col
customer	ID
customer	Code
customer	Name