Migrate XML data to Microsoft SQL Server 2016

Question

I want to migrate the data in XML File to MS SQL Server 2016

My Table fileds looks like this:

[propertyid], [type], [object], [keyname], [keyvalue]

Data Inside table will be mapped from XML and will look like this:

1, community, Private Community, name, Private Community   
2, community, Private Community, communityname, Private Community  
3, community, Private Community, server, chip.xyz.com:423132  
4, community, Private Community, windowtitle, Private Community  
5, community, Private Community, websiteurl, http://something

The XML File looks like below:

<?xml version="1.0" encoding="UTF-8"?>
<doc>
<object type="community">
<name>
<![CDATA[Private Community]]>
</name>
<communityname>
<![CDATA[Private Community]]>
</communityname>
<server>
<![CDATA[chip.xyz.com:423132]]>
</server>
<windowtitle>
<![CDATA[Private Community]]>
</windowtitle>
<websiteurl>
<![CDATA[http://something]]>
</websiteurl>
</object>
</doc>

Is there any way i can do it by scripts or any way because the above mentioned XML is just a part of big XML which i am using and having many records

will this help http://stackoverflow.com/questions/16838627/import-xml-into-sql-server — sashwat, Apr 07 '16 at 04:14

Shnugo · Accepted Answer · 2016-04-07T12:27:00.800

Try it like this:

DECLARE @xml XML=
'<?xml version="1.0" encoding="UTF-8"?>
<doc>
  <object type="community">
    <name>
Private Community
</name>
    <communityname>
Private Community
</communityname>
    <server>
chip.xyz.com:423132
</server>
    <windowtitle>
Private Community
</windowtitle>
    <websiteurl>
http://something
</websiteurl>
  </object>
</doc>';

I replace line breaks and trim the strings, hope this fits to your needs What I do not know: How is the structure, if there is more data. My query takes doc as root, takes doc/name as "type" and all node names as "keyname".

Using the "INTO MyNewlyCreatedTable" a table with the needed structure is created on the fly. If you have your table already, you take this line out and write above a fitting INSERT INTO MyTable(colName1,colName2,...) followed by the SELECT ...

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS propertyid
      ,LTRIM(RTRIM(REPLACE(REPLACE(Doc.value('(object/@type)[1]','varchar(max)'),CHAR(13),''),CHAR(10),''))) AS [type]
      ,LTRIM(RTRIM(REPLACE(REPLACE(Doc.value('(object/name)[1]','varchar(max)'),CHAR(13),''),CHAR(10),''))) AS [object]
      ,LTRIM(RTRIM(REPLACE(REPLACE(Nod.value('local-name(.)','varchar(max)'),CHAR(13),''),CHAR(10),''))) AS keyname
      ,LTRIM(RTRIM(REPLACE(REPLACE(Nod.value('.','varchar(max)'),CHAR(13),''),CHAR(10),''))) AS keyvalue
INTO MyNewlyCreatedTable
FROM @xml.nodes('/doc') AS A(Doc) 
CROSS APPLY Doc.nodes('object/*') AS B(Nod)

This is the content of the newly created table

SELECT * FROM MyNewlyCreatedTable;

+------------+-----------+-------------------+---------------+---------------------+
| propertyid | type      | object            | keyname       | keyvalue            |
+------------+-----------+-------------------+---------------+---------------------+
| 1          | community | Private Community | name          | Private Community   |
+------------+-----------+-------------------+---------------+---------------------+
| 2          | community | Private Community | communityname | Private Community   |
+------------+-----------+-------------------+---------------+---------------------+
| 3          | community | Private Community | server        | chip.xyz.com:423132 |
+------------+-----------+-------------------+---------------+---------------------+
| 4          | community | Private Community | windowtitle   | Private Community   |
+------------+-----------+-------------------+---------------+---------------------+
| 5          | community | Private Community | websiteurl    | http://something    |
+------------+-----------+-------------------+---------------+---------------------+

Just for testing

--CleanUp
DROP TABLE MyNewlyCreatedTable;

Migrate XML data to Microsoft SQL Server 2016

1 Answers1