Not picking up all XML values

[bikerboy718]

I am trying to run extract all of the order number values in my XML and for some reason I am only seeing the first order number (0000001) of the 3. Here is the code:

DECLARE @pOrderNumberXML VARCHAR(MAX)
SELECT @pOrderNumberXML = 
	'<root>
		<ordernumbers>
			<ordernumber>0000001</ordernumber> 
			<ordernumber>0000002</ordernumber>
			<ordernumber>0000003</ordernumber>
		</ordernumbers>
	</root>'

DECLARE @iXML INT

EXEC sp_xml_preparedocument @iXML OUTPUT, @pOrderNumberXML
	SELECT ordernumber
	FROM	OPENXML (@iXML, '/root/ordernumbers', 3)
			WITH (ordernumber VARCHAR(38))
			WHERE ordernumber IS NOT NULL 
EXEC sp_xml_removedocument @iXML

Any help would be gretly appreciated.



With Great Power Comes Great Responsibility!!!

Michael

 

[RyanEK]

Hi,

If you can update the actual XML to the following format, then the SQL below will work...

DECLARE @pOrderNumberXML VARCHAR(MAX)
SELECT @pOrderNumberXML =
    '<root>
       <ordernumbers>
			<ordernumber value = "0000001"/>
			<ordernumber value = "0000002"/>
			<ordernumber value = "0000003"/>
	   </ordernumbers>
    </root>'

DECLARE @iXML INT

EXEC sp_xml_preparedocument @iXML OUTPUT, @pOrderNumberXML
    SELECT *
    FROM    OPENXML (@iXML, 'root/ordernumbers/ordernumber')
            WITH (value VARCHAR(38))
            WHERE value IS NOT NULL
EXEC sp_xml_removedocument @iXML

Ryan

 

[bikerboy718]

Passing it via attributes does work. I currently use the method that you posted. But I would like to know if there is a way to pass the XML the way in which I provided in the post. I see a lot of XML in this format and am curious to find a method to insert it into SQL server.

With Great Power Comes Great Responsibility!!!

Michael