SQLTeam.com | Weblogs | Forums

XML help

Hello all,

I am still new to XML and did research but I am still struggling with querying the XML.

So here is the process I have achieved,

This is my XML product XML file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">
<Subcategories>
	<Subcategory ProductSubcategoryID = "18" Name="Bib-Shorts">
	<Products>
		<Product>
			<ProductID Type="1">855</ProductID>
			<ProductID Type="2">676</ProductID>
			<Name>Men's Bib-Short, s</Name>
			<ProductNumber>SB-M891-S</ProductNumber>
			<ListPrice>89.9900</ListPrice>
			<ModifiedDate>2008-03-11T10:01:36.827</ModifiedDate>
		</Product>
	</Products>
	</Subcategory>
</Subcategories>

Here is my working stored procedure:

ALTER PROCEDURE [dbo].[Test]
@xmlFileName VARCHAR(300)

AS
BEGIN

SET NOCOUNT ON;

DECLARE @xml XML,@hdoc INT 

CREATE TABLE XmlImportTest
	(
		xmlFileName VARCHAR(300),
		xml_data xml
	)
	

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC	('
			INSERT INTO XmlImportTest(xmlFileName, xml_data)
			SELECT ''' + @xmlFileName + ''', xmlData 
			FROM
			(				
				SELECT  CONVERT (XML, BulkColumn,2)	
				FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
			) AS FileImport (XMLDATA)
		 ')

SELECT xml_data  
FROM XmlImportTest
--WITH (ProductSubcategoryID VARCHAR(MAX) 'Subcategory/@ProductSubcategoryID')

DROP TABLE XmlImportTest

END

The parameter is:
@xmlFileName = 'C:\Users\Melinda\Desktop\Product2\Product.xml'

and here is the working result:

What I am trying to achieve, I want to extract this XML file and return as sql result set in columns and rows. To get as below result in SQL:

How do I update my stored proc? Can anyone help me on this?

Thank you so much all...

Hi yosiasz,

Thanks for your responded, with query without the parameter, I know how to handle that (just like the posting I did before) but the stored proc that I am working on is now using the parameter and I dont know how to grab the xml result and turn it as a sql data in rows and columns.

Thank you Yosiasz

The answer has already been answered for you in that previous post. It is a matter of you doing just a little bit more leg work. Think it through from that post and I am sure you can figure it out. That is the fn part of learning new technology!
Try different things and post back here what you have tried

Sure, thanks for the direction yosiasz, I will see the post I did and you are correct, I need to break this so I can understand it, I will post it when I am get there...thanks yosiasz.