I am working to extract below xml file using sql query. I can extract them out if the element is single but the issue I am having is I have 2 Products' element which is now return the same value.
and here is my sql query with single element of Product:
DECLARE @xml XML
SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml',SINGLE_BLOB) AS Products(Product)
DECLARE @hdoc INT
Exec sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH ( ProductID INT,
Name VARCHAR(100),
ProductNumber VARCHAR(100),
ListPrice FLOAT,
ModifiedDate DATETIME)
Hi yosiasz, I updated the Product elements that has two different attribute. So, those two attribute are the reason why the two ProductID appears twice. Thank you yosiasz.
This will not be good for you in the future when you will have more ProductId with different types. I would reconsider this design of your xml if at all possible. otherwise you will need to do something more dynamic if the future you will have product type 1, 2, 3, N
DECLARE @xml XML
SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK 'C:\xml\Products.xml',SINGLE_BLOB) AS Products(Product)
DECLARE @hdoc INT
Exec sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH ( ProductID INT 'ProductID[@type=("1")]',
ProductID2 INT 'ProductID[@type=("2")]',
Name VARCHAR(100),
ProductNumber VARCHAR(100),
ListPrice FLOAT,
ModifiedDate DATETIME)
SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml',SINGLE_BLOB) AS Products(Product)
If I want to create the 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml' as a parameter.
So, I am thinking like this:
SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK @path,SINGLE_BLOB) AS Products(Product)
Do you know how to replace the string of 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml' with @parameter?
DECLARE @xml XML, @xmlFileName varchar(25) = 'C:\xml\Products.xml', @hdoc INT
create TABLE #XmlImportTest(xmlFileName varchar(50), 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 *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
select @xml = xml_data FROM #XmlImportTest;
Exec sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH ( ProductID INT 'ProductID[@type=("1")]',
ProductID2 INT 'ProductID[@type=("2")]',
Name VARCHAR(100),
ProductNumber VARCHAR(100),
ListPrice FLOAT,
ModifiedDate DATETIME)
DROP TABLE #XmlImportTest
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">
So, some of the Product xml files work with above query but giving me an error of:
Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.
I know this has something to do with the SELECT CONVERT (XML, BulkColumn,2) or SELECT CONVERT (XML, BulkColumn,1), how do I handle this issue when XML file has the header like:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">