Put data from XML into SQL Server database

Question

I need to put all the data from XML into a SQL Server 2012 database.

<Columns>
   <TC><Name>Time</Name><DataType>System.DateTime</DataType></TC>
   <TC><Name>Name</Name><DataType>System.String</DataType></TC>
   <TC><Name>State</Name><DataType>System.String</DataType></TC>
   <TC><Name>Message</Name><DataType>System.String</DataType></TC>
</Columns>

<Rows>
  <TR>
    <Fields>
      <Field>2013-06-24</Field>
      <Field>PrjTest</Field>
      <Field>Started</Field>
      <Field>application starting,no exception</Field>
    </Fields>
  </TR>
  <TR>
    <Fields>
      <Field>2013-06-24</Field>
      <Field>PrjTest1</Field>
      <Field>Started</Field>
      <Field>application starting,no exception</Field>
    </Fields>
  </TR>
  <TR>
    <Fields>
      <Field>2013-06-24</Field>
      <Field>PrjTest2</Field>
      <Field>Completed</Field>
      <Field>application starting,no exception</Field>
   </Fields>
  </TR>
</Rows>

<Columns>...</Columns> section there will be my table schema with name <Name> and type <DataType>

Then in rows section, under <TR> I got all the values, the values are enclosed in <Field> tag.

Need to put all the data from rows section into a SQL Server table. As I have the schema for the table fixed here so no need to create the table dynamically. Need to put the data according to the table schema.

Note: XML file can be of size 25MB.

Also any pointers to start will be useful.

Have you tried the SQL Server documentation? Just asking.

C. M. Sperberg-McQueen
– C. M. Sperberg-McQueen

2013-06-25 14:26:01 +00:00
Commented Jun 25, 2013 at 14:26 — C. M. Sperberg-McQueen
– C. M. Sperberg-McQueen, Commented Jun 25, 2013 at 14:26

James A Mohler · Accepted Answer · 2013-09-29 23:20:07Z

1

DECLARE @xmlData xml = '...'


SELECT
   Tbl.Col.value('(Fields/Field)[1]', 'datetime')   AS [Time],
   Tbl.Col.value('(Fields/Field)[2]', 'nvarchar(max)')  AS [Name],
   Tbl.Col.value('(Fields/Field)[3]', 'nvarchar(max)')  AS [State],
   Tbl.Col.value('(Fields/Field)[4]', 'nvarchar(max)')  AS [Message]

FROM @xmlData.nodes('/Rows/TR') Tbl(Col)

.nodes is used to convert XML data into rows.

SQLFiddle:

http://sqlfiddle.com/#!6/d41d8/8428/0

edited Sep 29, 2013 at 23:20

answered Sep 29, 2013 at 23:14

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James A Mohler Over a year ago

@JohnSaunders: I have now added a link to SQLFiddle

Collectives™ on Stack Overflow

Put data from XML into SQL Server database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related