Import THIS xml file in an SQL DB

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  February 19, 2021 at 1:16 pm

  #3847291

  Dear all!

  I have tried to use the seach function, but not found anything i could understand.

  What i need is to import the attached xml file into two related tables.

  one for <table>

  one for <columns>

  with the relation still there.

  But how to?

   

  Best regards

   

  Edvard Korsbæk

  Attachments:

  You must be logged in to view attached files.

• Ken McKelvey

  SSCoach

  Points: 18901

  More actions

  February 20, 2021 at 1:24 pm

  #3847705

  I find XML a pain.

  Normally I start with nodes and once I get that right I then translate it to OPENXML as it seems faster even though you have to be careful to release the memory.

  As your XML contains a namespace you will need to use XMLNAMESPACES to get anything.

  Assuming the xml file is on the same machine as SQL Server, something like the following should get you started:

  WITH XMLNAMESPACES('http://www.sa.dk/xmlns/diark/1.0' as x)
  SELECT N.XN.value('x:name[1]','varchar(80)') AS [name]
  ,N.XN.value('x:folder[1]','varchar(80)') AS [folder]
  ,N.XN.value('x:description[1]','nvarchar(80)') AS [description]
  ,N.XN.value('x:rows[1]','int') AS [rows]
  FROM (SELECT CAST(T.xmlcol AS xml)
        FROM OPENROWSET(BULK 'C:\tmp\tableIndex.xml', SINGLE_BLOB) AS T(xmlcol)) AS X(xmlcol)
  CROSS APPLY X.xmlcol.nodes(N'x:siardDiark/x:tables/x:table') AS N (XN);

  Good luck!

  • This reply was modified 4 years, 3 months ago by Ken McKelvey.