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