Remove From My Forums

Exam Practice for OPENXML

Question

I have function like this, which works perfectly.

USE TSQL2012;
GO
IF OBJECT_ID(N'Meta.SampleNVARCHARX', 'FN') IS NOT NULL DROP FUNCTION Meta.SampleNVARCHARX;
GO
CREATE FUNCTION Meta.SampleNVARCHARX ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @x AS NVARCHAR(MAX); 
SELECT @x =  
N'<?xml version="1.0"?> 
<CountryStateCities xmlns:cc="CanadianCities" xmlns:uc="AmericanCities">
	<Countries>
		<cc:Country CountryName="Canada">
			<cc:Provinces>
				<cc:Province ProvinceName="Alberta">
					<cc:Cities>
						<cc:City CityName="AIRDRIE" />
						<cc:City CityName="BARRHEAD" />
						<cc:City CityName="BEAUMONT" />
						<cc:City CityName="BONNYVILLE" />
						<cc:City CityName="BROOKS" />
						<cc:City CityName="CALGARY" />
						<cc:City CityName="CAMROSE" />
						<cc:City CityName="CANMORE" />
						<cc:City CityName="CARDSTON" />
						<cc:City CityName="CHESTERMERE" />
						<cc:City CityName="COCHRANE" />
						<cc:City CityName="COLD LAKE" />
						<cc:City CityName="CORONATION" />
						<cc:City CityName="DRAYTON VALLEY" />
						<cc:City CityName="DRUMHELLER" />
						<cc:City CityName="EDMONTON" />
						<cc:City CityName="ELK POINT" />
						<cc:City CityName="FORT MCMURRAY" />
						<cc:City CityName="FORT SASKATCHEWAN" />
						<cc:City CityName="GRANDE PRAIRIE" />
						<cc:City CityName="HANNA" />
						<cc:City CityName="HIGH PRAIRIE" />
						<cc:City CityName="HIGH RIVER" />
						<cc:City CityName="JASPER" />
						<cc:City CityName="LACOMBE" />
						<cc:City CityName="LEDUC" />
						<cc:City CityName="LETHBRIDGE" />
						<cc:City CityName="LLOYDMINSTER" />
						<cc:City CityName="MAYERTHORPE" />
						<cc:City CityName="MEDICINE HAT" />
						<cc:City CityName="OKOTOKS" />
						<cc:City CityName="OLDS" />
						<cc:City CityName="OYEN" />
						<cc:City CityName="PEACE RIVER" />
						<cc:City CityName="RED DEER" />
						<cc:City CityName="SHERWOOD PARK" />
						<cc:City CityName="SPRUCE GROVE" />
						<cc:City CityName="ST ALBERT" />
						<cc:City CityName="ST PAUL" />
						<cc:City CityName="STETTLER" />
						<cc:City CityName="THREE HILLS" />
						<cc:City CityName="VEGREVILLE" />
						<cc:City CityName="VERMILION" />
						<cc:City CityName="WESTLOCK" />
						<cc:City CityName="WETASKIWIN" />
					</cc:Cities>
				</cc:Province>
			</cc:Provinces>
		</cc:Country>
	  <uc:Country CountryName="United States">
	    <uc:States>
	    	<uc:State StateName="Alabama">
		    	<uc:Cities>
		      	<uc:City CityName="BIRMINGHAM" />
		      	<uc:City CityName="FAIRFIELD" />
		      	<uc:City CityName="GARDENDALE" />
		      	<uc:City CityName="MONTGOMERY" />
		      	<uc:City CityName="TRUSSVILLE" />
		      </uc:Cities>
	      </uc:State>
		    <uc:State StateName="Arizona">
		    	<uc:Cities>
			      <uc:City CityName="CAVE CREEK" />
			      <uc:City CityName="FOUNTAIN HILLS" />
			      <uc:City CityName="MESA" />
		      </uc:Cities>
		    </uc:State>
		    <uc:State StateName="Arkansas">
		      <uc:Cities>
		      	<uc:City CityName="CABOT" />
		      	<uc:City CityName="MAUMELLE" />
		      </uc:Cities>
		    </uc:State>
	    </uc:States>
	  </uc:Country>
	</Countries>  
</CountryStateCities>'
	-- Return the result of the function
	RETURN @x
END
GO

It has two countries.

I am trying to get the following test harness to work, but it only ever returns NULL. Some tests come close - I get two rows with NULL, one for each country. I tried the element centric flag [1], the attribute centric flag [2], and both [11].
I am expecting the following rowset. Can anyone help me figure this out from SSMS 2012?

Country
--------------
Canada
United States

DECLARE @XMLDocument AS NVARCHAR(MAX);
DECLARE @DocHandle AS INTEGER;

SET @XMLDocument = Meta.SampleNVARCHARX ();
PRINT @XMLDocument
EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XMLDocument, '<CountryStateCities xmlns:cc="CanadianCities" xmlns:uc="AmericanCities"/>';

SELECT *
FROM OPENXML (@DocHandle, '/CountryStateCities/Countries/cc:Country', 11)
     WITH ([cc:Country] NVARCHAR(25));

EXEC sys.sp_xml_removedocument @DocHandle;

Thursday, October 8, 2015 4:48 PM

Answers

The problem is the usage of XML namespaces. A simplified example:

DECLARE @x AS XML = N'
<CountryStateCities xmlns:cc="CanadianCities" xmlns:uc="AmericanCities">
	<Countries>
		<cc:Country CountryName="Canada">
			<cc:Provinces>
				<cc:Province ProvinceName="Alberta">
					<cc:Cities>
						<cc:City CityName="AIRDRIE" />
						<cc:City CityName="BARRHEAD" />
						<cc:City CityName="BEAUMONT" />
						<cc:City CityName="BONNYVILLE" />
						<cc:City CityName="BROOKS" />
						<cc:City CityName="CALGARY" />
						<cc:City CityName="CAMROSE" />
						<cc:City CityName="CANMORE" />
						<cc:City CityName="CARDSTON" />
						<cc:City CityName="CHESTERMERE" />
						<cc:City CityName="COCHRANE" />
						<cc:City CityName="COLD LAKE" />
						<cc:City CityName="CORONATION" />
						<cc:City CityName="DRAYTON VALLEY" />
						<cc:City CityName="DRUMHELLER" />
						<cc:City CityName="EDMONTON" />
						<cc:City CityName="ELK POINT" />
						<cc:City CityName="FORT MCMURRAY" />
						<cc:City CityName="FORT SASKATCHEWAN" />
						<cc:City CityName="GRANDE PRAIRIE" />
						<cc:City CityName="HANNA" />
						<cc:City CityName="HIGH PRAIRIE" />
						<cc:City CityName="HIGH RIVER" />
						<cc:City CityName="JASPER" />
						<cc:City CityName="LACOMBE" />
						<cc:City CityName="LEDUC" />
						<cc:City CityName="LETHBRIDGE" />
						<cc:City CityName="LLOYDMINSTER" />
						<cc:City CityName="MAYERTHORPE" />
						<cc:City CityName="MEDICINE HAT" />
						<cc:City CityName="OKOTOKS" />
						<cc:City CityName="OLDS" />
						<cc:City CityName="OYEN" />
						<cc:City CityName="PEACE RIVER" />
						<cc:City CityName="RED DEER" />
						<cc:City CityName="SHERWOOD PARK" />
						<cc:City CityName="SPRUCE GROVE" />
						<cc:City CityName="ST ALBERT" />
						<cc:City CityName="ST PAUL" />
						<cc:City CityName="STETTLER" />
						<cc:City CityName="THREE HILLS" />
						<cc:City CityName="VEGREVILLE" />
						<cc:City CityName="VERMILION" />
						<cc:City CityName="WESTLOCK" />
						<cc:City CityName="WETASKIWIN" />
					</cc:Cities>
				</cc:Province>
			</cc:Provinces>
		</cc:Country>
	  <uc:Country CountryName="United States">
	    <uc:States>
	    	<uc:State StateName="Alabama">
		    	<uc:Cities>
		      	<uc:City CityName="BIRMINGHAM" />
		      	<uc:City CityName="FAIRFIELD" />
		      	<uc:City CityName="GARDENDALE" />
		      	<uc:City CityName="MONTGOMERY" />
		      	<uc:City CityName="TRUSSVILLE" />
		      </uc:Cities>
	      </uc:State>
		    <uc:State StateName="Arizona">
		    	<uc:Cities>
			      <uc:City CityName="CAVE CREEK" />
			      <uc:City CityName="FOUNTAIN HILLS" />
			      <uc:City CityName="MESA" />
		      </uc:Cities>
		    </uc:State>
		    <uc:State StateName="Arkansas">
		      <uc:Cities>
		      	<uc:City CityName="CABOT" />
		      	<uc:City CityName="MAUMELLE" />
		      </uc:Cities>
		    </uc:State>
	    </uc:States>
	  </uc:Country>
	</Countries>  
</CountryStateCities>';

WITH XMLNAMESPACES ( 'CanadianCities' AS cc )
	SELECT	Country.query('.')
	FROM	@x.nodes('/CountryStateCities/Countries/cc:Country') A ( Country );

Proposed as answer by Eric__Zhang Friday, October 9, 2015 4:44 AM
Marked as answer by Eric__Zhang Wednesday, October 28, 2015 9:40 AM

Thursday, October 8, 2015 8:10 PM

If you want to stick with OPENXML then I think you are just missing the @ sign to access an attribute, eg this worked for me:

SELECT *
FROM OPENXML ( @DocHandle, '(/CountryStateCities/Countries/cc:Country)', 1 )
WITH ( [Country] NVARCHAR(25) '@CountryName' )
UNION ALL
SELECT *
FROM OPENXML ( @DocHandle, '(/CountryStateCities/Countries/uc:Country)', 1 )
WITH ( [Country] NVARCHAR(25) '@CountryName' )

EXEC sys.sp_xml_removedocument @DocHandle

If you want to try the XML datatype and its methods, then you can use schema wildcard or WITH XMLNAMESPACES as Stefan has suggested. Something like this should work:

SELECT *
FROM OPENXML ( @DocHandle, '(/CountryStateCities/Countries/cc:Country)', 1 )
WITH ( [Country] NVARCHAR(25) '@CountryName' )
UNION ALL
SELECT *
FROM OPENXML ( @DocHandle, '(/CountryStateCities/Countries/uc:Country)', 1 )
WITH ( [Country] NVARCHAR(25) '@CountryName' )

EXEC sys.sp_xml_removedocument @DocHandle


-- XML datatype methods
DECLARE @x XML = @XMLDocument

-- Schema wilcard
SELECT Country.value('@CountryName', 'NVARCHAR(25)')
FROM @x.nodes('/CountryStateCities/Countries/*:Country') A ( Country );

-- Sequence expression
;WITH XMLNAMESPACES ( 'CanadianCities' AS cc, 'AmericanCities' AS uc )
SELECT Country.value('@CountryName', 'NVARCHAR(25)')
FROM @x.nodes('/CountryStateCities/Countries/cc:Country, /CountryStateCities/Countries/uc:Country') A ( Country );

Marked as answer by Eric__Zhang Wednesday, October 28, 2015 9:40 AM

Friday, October 9, 2015 1:00 PM

Answerer

All replies