SQLTeam.com | Weblogs | Forums

XML help


#1

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...


#2

#3

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


#4

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


#5

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.