проблема с разбором xml

sashenka
Дата: 24.01.2009 18:32:24
есть код
DECLARE @GameXml xml

SET @GameXml = '<?xml version="1.0" encoding="UTF-8"?>
<game creationDate="2008-11-12" gameID="4elements" xmlns="game"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="game http://games-preview.real.com/rnfeedgamesdl/XMLSchemas/game.xsd">
    <gameTypeList>
        <gameType>download</gameType>
    </gameTypeList>
    <downloadableGameData>
        <gameName>4 Elements</gameName>
        <description>
            <long>Save an ancient kingdom from certain destruction in 4 Elements, an original game full of unique puzzle challenges.  In a faraway land, where magic once ruled, four altars dedicated to the elements of earth, air, fire, and water have been destroyed, unleashing a powerful force.  Arrange game pieces into matching groups of three or more to unlock the four books of magic, collect 16 mysterious cards, and restore the kingdom to life.  Featuring more than 60 vivid levels,  innovative bonus rounds, spectacular visual effects, and a helpful forest fairy, the matching fun in 4 Elements is sure to challenge even the most seasoned puzzle player.  Embark on this amazing journey today.</long>
            <medium>Save an ancient kingdom from certain destruction in 4 Elements, an original game full of unique puzzle challenges.  Featuring more than 60 vivid levels, the matching fun in 4 Elements is sure to challenge even the most seasoned puzzle player.  Embark on this amazing journey today.</medium>
            <short>Use your puzzle skills to save an ancient kingdom in 4 Elements.</short>
            <headline>An Amazing Journey of Puzzle Fun</headline>
        </description>
        <images>
            <gameImage>4elements_240x135.jpg</gameImage>
            <thumbnailImage>4elements_80x45.jpg</thumbnailImage>
            <featureImage>4elements_146x110.jpg</featureImage>
            <squareImage>4elements_150x150.jpg</squareImage>
            <tinySquareImage>4elements_50x50.jpg</tinySquareImage>
            <promoPageImage>4elements_750x550.jpg</promoPageImage>
        </images>
        <featureList title="Full Version Features">
            <feature>Unlimited Play</feature>
            <feature>Over 60 Challenging Levels</feature>
            <feature>Rich Landscape of Magical Creatures</feature>
            <feature>Incredible Visual Effects and Fantastic Bonuses</feature>
            <feature>Innovative Storyline with Amazing Animations</feature>
        </featureList>
        <developer>
            <name>Terminal Studio</name>
            <countryOfOrigin>Russia</countryOfOrigin>
        </developer>
        <purchaseType>tryAndBuy</purchaseType>
        <releaseDate>2008-09-10</releaseDate>
        <keywords>
            <primaryGenreKeyword>Puzzle</primaryGenreKeyword>
        </keywords>
        <sponsored>false</sponsored>
        <customerRating units="Stars">5.0</customerRating>
        <editorRating units="Stars">5.0</editorRating>
        <productID>21092</productID>
        <priceID>4</priceID>
        <downloadSize units="MB">
            <freeSize>45.2</freeSize>
            <paySize>45.2</paySize>
        </downloadSize>
        <systemRequirements>
            <processor>
                <speed units="GHz">1.0</speed>
                <description>Pentium 3 or equivalent</description>
            </processor>
            <operatingSystemList>
                <operatingSystem description="Windows 98"/>
                <operatingSystem description="Windows ME"/>
                <operatingSystem description="Windows 2000"/>
                <operatingSystem description="Windows XP"/>
                <operatingSystem description="Windows Vista"/>
            </operatingSystemList>
            <ram units="MB">256.0</ram>
            <diskSpaceRequired units="MB">170.3</diskSpaceRequired>
            <graphics>
                <minDirectXVersion>8.0</minDirectXVersion>
                <required3DVideoCard>false</required3DVideoCard>
            </graphics>
        </systemRequirements>
        <screenshotList title="4 Elements">
            <imageSet title="4 Elements - 1">
                <largeImage title="4 Elements - 1">
                    <url>4elements_screenshot_1_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 1">
                    <url>4elements_screenshot_1_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 2">
                <largeImage title="4 Elements - 2">
                    <url>4elements_screenshot_2_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 2">
                    <url>4elements_screenshot_2_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 3">
                <largeImage title="4 Elements - 3">
                    <url>4elements_screenshot_3_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 3">
                    <url>4elements_screenshot_3_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 4">
                <largeImage title="4 Elements - 4">
                    <url>4elements_screenshot_4_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 4">
                    <url>4elements_screenshot_4_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 5">
                <largeImage title="4 Elements - 5">
                    <url>4elements_screenshot_5_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 5">
                    <url>4elements_screenshot_5_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 6">
                <largeImage title="4 Elements - 6">
                    <url>4elements_screenshot_6_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 6">
                    <url>4elements_screenshot_6_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
        </screenshotList>
    </downloadableGameData>
</game>
'

--alter procedure dbo.SaveDownloadGame 
	--@GameXml xml
--as
--begin
	DECLARE @error int, @ihdoc int	--xml document handler id

-- start prepare xml document and create handler 
	BEGIN try
		EXEC dbo.sp_xml_preparedocument @ihdoc OUTPUT, @GameXml
	END try
	BEGIN catch 	
		SET @ihdoc = 0;
		SET @error = -1 		
	END catch
-- end prepare xml document and create handler

-- start select data from xml if handler was created
	IF (@ihdoc <> 0)
	BEGIN 	
		BEGIN try
			
			select gameid
			from openxml(@ihdoc, '/')
			with(gameid varchar(256))

		END try
		BEGIN catch
			SET @error = -2 		
		END catch
-- end select data from xml if handler was created

----start remove xml document and handler
--		BEGIN try
--			EXEC sp_xml_removedocument @ihdoc 	
--		END try
--		BEGIN catch
--			SET @error = -3 		
--		END catch
	END

--end


почемуто gameid в селекте = null хотя вроде все правильно. про попытку достучаться к уровням ниже вообще молчу. в чем может быть проблема?
Ennor Tiegael
Дата: 25.01.2009 04:54:11
Проблема в пространстве имен. Если из корневого тэга убрать атрибуты
xmlns="game"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="game http://games-preview.real.com/rnfeedgamesdl/XMLSchemas/game.xsd"
, то все начинает работать.

Не являясь спецом по XML ни в коей мере, могу только посоветовать дождаться местных гуру. Ну или разобраться самому, поскольку источник проблемы хотя бы локализован...
sashenka
Дата: 25.01.2009 09:14:32
пасиб большое. уже не первый раз сталкиваюсь с данной проблемой, но пока решал обходным путем(удалением этих тегов). может кто подскажет как работать с xml при наличии данных атрибутов?
sashenka
Дата: 25.01.2009 10:37:11
разобрался
вот так работает
DECLARE @GameXml xml

SET @GameXml = '<?xml version="1.0" encoding="UTF-8"?>
<game creationDate="2008-11-12" gameID="4elements" xmlns="game"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="game http://games-preview.real.com/rnfeedgamesdl/XMLSchemas/game.xsd">
    <gameTypeList>
        <gameType>download</gameType>
    </gameTypeList>
    <downloadableGameData>
        <gameName>4 Elements</gameName>
        <description>
            <long>Save an ancient kingdom from certain destruction in 4 Elements, an original game full of unique puzzle challenges.  In a faraway land, where magic once ruled, four altars dedicated to the elements of earth, air, fire, and water have been destroyed, unleashing a powerful force.  Arrange game pieces into matching groups of three or more to unlock the four books of magic, collect 16 mysterious cards, and restore the kingdom to life.  Featuring more than 60 vivid levels,  innovative bonus rounds, spectacular visual effects, and a helpful forest fairy, the matching fun in 4 Elements is sure to challenge even the most seasoned puzzle player.  Embark on this amazing journey today.</long>
            <medium>Save an ancient kingdom from certain destruction in 4 Elements, an original game full of unique puzzle challenges.  Featuring more than 60 vivid levels, the matching fun in 4 Elements is sure to challenge even the most seasoned puzzle player.  Embark on this amazing journey today.</medium>
            <short>Use your puzzle skills to save an ancient kingdom in 4 Elements.</short>
            <headline>An Amazing Journey of Puzzle Fun</headline>
        </description>
        <images>
            <gameImage>4elements_240x135.jpg</gameImage>
            <thumbnailImage>4elements_80x45.jpg</thumbnailImage>
            <featureImage>4elements_146x110.jpg</featureImage>
            <squareImage>4elements_150x150.jpg</squareImage>
            <tinySquareImage>4elements_50x50.jpg</tinySquareImage>
            <promoPageImage>4elements_750x550.jpg</promoPageImage>
        </images>
        <featureList title="Full Version Features">
            <feature>Unlimited Play</feature>
            <feature>Over 60 Challenging Levels</feature>
            <feature>Rich Landscape of Magical Creatures</feature>
            <feature>Incredible Visual Effects and Fantastic Bonuses</feature>
            <feature>Innovative Storyline with Amazing Animations</feature>
        </featureList>
        <developer>
            <name>Terminal Studio</name>
            <countryOfOrigin>Russia</countryOfOrigin>
        </developer>
        <purchaseType>tryAndBuy</purchaseType>
        <releaseDate>2008-09-10</releaseDate>
        <keywords>
            <primaryGenreKeyword>Puzzle</primaryGenreKeyword>
        </keywords>
        <sponsored>false</sponsored>
        <customerRating units="Stars">5.0</customerRating>
        <editorRating units="Stars">5.0</editorRating>
        <productID>21092</productID>
        <priceID>4</priceID>
        <downloadSize units="MB">
            <freeSize>45.2</freeSize>
            <paySize>45.2</paySize>
        </downloadSize>
        <systemRequirements>
            <processor>
                <speed units="GHz">1.0</speed>
                <description>Pentium 3 or equivalent</description>
            </processor>
            <operatingSystemList>
                <operatingSystem description="Windows 98"/>
                <operatingSystem description="Windows ME"/>
                <operatingSystem description="Windows 2000"/>
                <operatingSystem description="Windows XP"/>
                <operatingSystem description="Windows Vista"/>
            </operatingSystemList>
            <ram units="MB">256.0</ram>
            <diskSpaceRequired units="MB">170.3</diskSpaceRequired>
            <graphics>
                <minDirectXVersion>8.0</minDirectXVersion>
                <required3DVideoCard>false</required3DVideoCard>
            </graphics>
        </systemRequirements>
        <screenshotList title="4 Elements">
            <imageSet title="4 Elements - 1">
                <largeImage title="4 Elements - 1">
                    <url>4elements_screenshot_1_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 1">
                    <url>4elements_screenshot_1_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 2">
                <largeImage title="4 Elements - 2">
                    <url>4elements_screenshot_2_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 2">
                    <url>4elements_screenshot_2_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 3">
                <largeImage title="4 Elements - 3">
                    <url>4elements_screenshot_3_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 3">
                    <url>4elements_screenshot_3_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 4">
                <largeImage title="4 Elements - 4">
                    <url>4elements_screenshot_4_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 4">
                    <url>4elements_screenshot_4_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 5">
                <largeImage title="4 Elements - 5">
                    <url>4elements_screenshot_5_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 5">
                    <url>4elements_screenshot_5_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
            <imageSet title="4 Elements - 6">
                <largeImage title="4 Elements - 6">
                    <url>4elements_screenshot_6_640x480.jpg</url>
                    <imageType>ragallery-im-lgimage</imageType>
                    <height>480</height>
                    <width>640</width>
                </largeImage>
                <thumbnailImage title="4 Elements - 6">
                    <url>4elements_screenshot_6_120x90.jpg</url>
                    <imageType>ragallery-im-thumbimage</imageType>
                    <height>90</height>
                    <width>120</width>
                </thumbnailImage>
            </imageSet>
        </screenshotList>
    </downloadableGameData>
</game>
'

--alter procedure dbo.SaveDownloadGame 
	--@GameXml xml
--as
--begin
	DECLARE @error int, @ihdoc int	--xml document handler id

-- start prepare xml document and create handler 
	BEGIN try
		EXEC dbo.sp_xml_preparedocument @ihdoc OUTPUT, @GameXml, '<root xmlns:a="game"/>'
	END try
	BEGIN catch 	
		SET @ihdoc = 0;
		SET @error = -1 		
	END catch
-- end prepare xml document and create handler

-- start select data from xml if handler was created
	IF (@ihdoc <> 0)
	BEGIN 	
		BEGIN try
			
			select *
			from openxml(@ihdoc, '/a:game')
			with([gameid] varchar(256) '@gameID')

		END try
		BEGIN catch
			SET @error = -2 		
		END catch
-- end select data from xml if handler was created

----start remove xml document and handler
--		BEGIN try
--			EXEC sp_xml_removedocument @ihdoc 	
--		END try
--		BEGIN catch
--			SET @error = -3 		
--		END catch
	END

--end