Fetching data from XML document into SQL table, empty table when using OPENROWSET for retrieving attribute from XML

I need to insert attributes from a XML file into a SQL table. I have roamed Stackoverflow to find a solution but nothing I tried seems to work.

I have tried with and without defining and using the namespace and also all kind of different node-path combinations for navigating through the XML.

Here is a part of the Xml which I would like to retrieve the attributes from:

``<export xmlns="http://www.arcticgroup.se/tariff/arctictariff/export" xmlns:at="http://www.arcticgroup.se/tariff/arctictariff/export" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.arcticgroup.se/tariff/arctictariff/export GoodsNomenclature.xsd ">
  <id>e37c6378-892a-4c25-b3fe-d0f3fcdf5a29</id>
  <exportType>GoodsNomenclatureObject</exportType>
  <parameters />
  <items>
    <goodsNomenclature at:goodsNomenclatureCode="0101109090" at:dateEnd="2011-06-30" at:national="0" at:productLineSuffix="80" at:SID="74778" at:dateStart="2002-01-01" at:statisticalIndicator="0" at:changeType="U">
      <goodsNomenclatureIndent at:national="0" at:quantityIndents="03" at:SID="74084" at:dateStart="2002-01-01" />
      <goodsNomenclatureDescriptionPeriod at:national="0" at:SID="92833" at:dateStart="2002-01-01">
        <goodsNomenclatureDescription at:description="andere" at:languageId="NL" at:national="0" />
        <goodsNomenclatureDescription at:description="Other" at:languageId="EN" at:national="0" />
      </goodsNomenclatureDescriptionPeriod>
    </goodsNomenclature>
  </items>
</export>``

This is the last code which I tried:

INSERT INTO TblGoodsNomenclature(XMLData, CreatedDate)
SELECT Convert(Xml, BulkColumn) as BulkColumn, GETDATE()
FROM OPENROWSET(Bulk '\shareappc$tempGoodsNomenclature.xml', Single_Blob) as x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XmlData FROM TblGoodsNomenclature

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<export xmlns="http://www.arcticgroup.se/tariff/arctictariff/export" xmlns:at="http://www.arcticgroup.se/tariff/arctictariff/export"/>'

INSERT INTO NomenclatureCodes (NomenclatureCode, Description, DescriptionLanguage)
SELECT NomenclatureCode, Description, DescriptionLanguage
    FROM OPENXML(@hDoc, '/items/goodsNomenclature/goodsNomenclatureDescriptionPeriod/goodsNomenclatureDescription/at:node')
    WITH 
    (
        NomenclatureCode INT '@at:goodsNomenclatureCode',
        Description [varchar](100) '@at:description',
        DescriptionLanguage [varchar](5) '@at:languageID'

    )

The result which I am getting now is just an empty table, no error messages. What I would like to have is the ‘goodsNomeclatureCode’, the ‘description’ and the ‘languageID’ in a table.

Answer

As mentioned in the comments, you’re far better off using XQUERY here. As you have multiple namespaces as well we need to declare them too. This gives you the following query:

WITH XMLNAMESPACES (DEFAULT 'http://www.arcticgroup.se/tariff/arctictariff/export',
                    'http://www.arcticgroup.se/tariff/arctictariff/export' AS [at])
SELECT GN.XMLData,
       EI.gN.value('@at:goodsNomenclatureCode[1]','varchar(15)') AS goodsNomenclatureCode
FROM dbo.TblGoodsNomenclature GN
     CROSS APPLY GN.XMLData.nodes('export/items/goodsNomenclature') EI(gN);

db<>fiddle

Advertisement

Answer