CODEWITHSUNDEEP
xmlsql-server-2012
usr021986
usr021986

Reputation: 3511

Put data from XML into SQL Server database

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.

Upvotes: 0

Views: 245

Answers (1)

James A Mohler
James A Mohler

Reputation: 11120

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

Upvotes: 1

Related Questions