Post reply

Import THIS xml file in an SQL DB

  • 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.

  • 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, 6 months ago by Ken McKelvey.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply