I have the following xml,
I try to get data to MSSQL
server table with OPENROWSET
and I can't get the product id value,
there is some different way to get the product id?
thanks,
XML Content:
<?xml version="1.0" encoding="UTF-8"?>
<manufacturer url="https://www.manufacturer.gr" name="https://www.manufacturer.gr" encoding="utf-8"><created_at>2021-12-08</created_at>
<products>
<product id="12289">
<name><![CDATA[ΠΑΡΑΜΥΘΙΑ ΜΕ ΖΩΑΚΙΑ]]></name>
<link>https://www.manufacturer.gr/el/paramythia-me-zwakia.html</link>
<image>
<image1>https://assets.manufacturer.gr/media/catalog/product/9/7/9789604969647_1.png</image1>
<image2>https://assets.manufacturer.gr/media/catalog/product/9/7/9789604969647_bc_1.png</image2>
<image3>https://assets.manufacturer.gr/media/catalog/product/9/7/9789604969647_3D_1.png</image3>
</image>
<category>
<![CDATA[category]]>
</category>
<series>
<![CDATA[Εικο]]></series>
<series_id>123</series_id>
<author>
<![CDATA[]]></author><author_id/><age><![CDATA[3-4]]></age><description><![CDATA[description]]></description>
<price>12.20</price><isbn>978-960-496-964-7</isbn>
<pages>122</pages>
<dimensions>22 x 28.2</dimensions>
<weight>0.8420</weight>
<instock>Y</instock>
<availability>Μη διαθέσιμο</availability>
<barcode>9789604969647</barcode>
<manufacturer><![CDATA[]]></manufacturer>
<fixed_price>ΕΚΤΟΣ ΕΝΙΑΙΑΣ ΤΙΜΗΣ</fixed_price>
</product>
</products>
</manufacturer>
SQL Code
DECLARE @input XML
SET @input = (SELECT * FROM OPENROWSET(BULK 'C:\content.xml', single_blob) AS b)
SELECT
cast(doc.col.query('id[1]')as nvarchar(100)) as id,
doc.col.value('name[1]', 'nvarchar(100)') AS title,
doc.col.value('description[1]', 'nvarchar(max)') AS description,
doc.col.value('age[1]', 'nvarchar(100)') AS age,
doc.col.value('category[1]', 'nvarchar(500)') AS category,
doc.col.value('price[1]', 'nvarchar(100)') AS price,
doc.col.value('tax[1]', 'float') AS tax,
doc.col.value('availability[1]', 'nvarchar(100)') AS availability,
doc.col.value('manufacturer[1]', 'nvarchar(100)') AS manufacturer,
doc.col.value('isbn[1]', 'nvarchar(100)') AS mpn,
doc.col.value('barcode[1]', 'nvarchar(100)') AS ean,
doc.col.value('weight[1]', 'nvarchar(100)') AS weight,
doc.col.value('fixed_price[1]', 'nvarchar(100)') AS fixed_price
FROM @input.nodes('manufacturer/products/product') doc(col)