0

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)
Nima Derakhshanjan
  • 1,380
  • 9
  • 24
  • 37
rama
  • 1
  • 1
    `id` is an attribute and not an element, so it must be selected with `@id`: `doc.col.value('@id[1]', 'nvarchar(100)')`. – Jeroen Mostert Dec 09 '21 at 15:19
  • Does this answer your question? [Xquery get value from attribute](https://stackoverflow.com/questions/2912062/xquery-get-value-from-attribute) – Charlieface Dec 10 '21 at 11:16
  • Yes i try it, but return nulls only, any suggestion? – rama Dec 13 '21 at 13:54
  • Retry Jeroen's suggestion. Replace the entire line `cast(doc.col.query('id[1]')as nvarchar(100)) as id,` with `doc.col.value('@id[1]', 'nvarchar(100)') as id,`. – AlwaysLearning Feb 05 '22 at 10:44

0 Answers0