How to insert from XML file into SQL server?

Question

How to insert from XML file into SQL Server if the format of file is like this?

I want columns Description, Alias, and Name.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE ldmlBCP47 SYSTEM "../../common/dtd/ldmlBCP47.dtd">
<ldmlBCP47> 
   <version number="$Revision: 6625 $"/> 
   <generation date="$Date: 2012-03-01 15:01:04 -0800 (Thu, 01 Mar 2012) $"/> 
   <keyword> 
      <key description="Time zone key" alias="timezone" name="tz"> 
         <type description="Andorra" alias="Europe/Andorra" name="adalv"/> 
         <type description="Dubai, United Arab Emirates" alias="Asia/Dubai" name="aedxb"/> 
         <type description="Kabul, Afghanistan" alias="Asia/Kabul" name="afkbl"/> 
         <type description="Antigua" alias="America/Antigua" name="aganu"/> 
         <type description="Anguilla" alias="America/Anguilla" name="aiaxa"/> 
         <type description="Tirane, Albania" alias="Europe/Tirane" name="altia"/>

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Dec 20, 2012 at 0:17

marc_s · Accepted Answer · 2012-12-20 06:13:20Z

Assuming you mean to fetch the three attributes from the <type> XML element, then you can try this:

DECLARE @input XML = '<ldmlBCP47> 
   <version number="$Revision: 6625 $"/> 
   <generation date="$Date: 2012-03-01 15:01:04 -0800 (Thu, 01 Mar 2012) $"/> 
   <keyword> 
      <key description="Time zone key" alias="timezone" name="tz"> 
         <type description="Andorra" alias="Europe/Andorra" name="adalv"/> 
         <type description="Dubai, United Arab Emirates" alias="Asia/Dubai" name="aedxb"/> 
         <type description="Kabul, Afghanistan" alias="Asia/Kabul" name="afkbl"/> 
         <type description="Antigua" alias="America/Antigua" name="aganu"/> 
         <type description="Anguilla" alias="America/Anguilla" name="aiaxa"/> 
         <type description="Tirane, Albania" alias="Europe/Tirane" name="altia"/> 
        </key>
    </keyword>
</ldmlBCP47>'

SELECT
    [Description] = XTbl.TypeNode.value('@description', 'varchar(50)'),
    [Alias] = XTbl.TypeNode.value('@alias', 'varchar(50)'),
    [Name] = XTbl.TypeNode.value('@name', 'varchar(50)')
FROM
    @input.nodes('/ldmlBCP47/keyword/key/type') AS XTbl(TypeNode)

Results in an output like:

enter image description here

Collectives™ on Stack Overflow

How to insert from XML file into SQL server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related