Import 'xml' into Sql Server

Question

I have a file that is structured like so:

<?xml version="1.0" encoding="UTF-8"?>
<EventSchedule>
    <Event Uid="2" Type="Main Event">
        <IsFixed>True</IsFixed>
        <EventKind>MainEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
    <Event>
    ...and so on
    </Event>
</EventSchedule>

I'm not entirely sure if it is valid XML, however I need to import it into SQL Server but everything I try doesn't seem to work.

Please could anyone point me in the right direction either with some example code or a recommendation on which method to use?

I'd ideally like to get the raw data into a flat table, along the lines of:

Name        | Type    | Duration | EventKind

The Muppets | TV_Show | 00:30:00 | MainEvent

Finally this is coming from fairly large files and I will need to import the regularly.

Thanks, pugu

How do you want the data to be composed once you have imported it? — paul
– paul, Commented May 30, 2013 at 14:19
Would you import it entirely as XML or parse it, build a schema based on XML structure and so on...? — Francesco De Lisi
– Francesco De Lisi, Commented May 30, 2013 at 14:22
I was hoping to get the data into a single table, accepting that in some cases there would be 'null' entries, for example if one of the Parameters wasn't present. Ideally I would like to do it with some T-SQL that I can run as a scheduled task. I tried using SSIS but I couldn't get it to recognise the XML. — pugu
– pugu, Commented May 30, 2013 at 14:31

marc_s · Accepted Answer · 2013-05-30 16:01:28Z

65

Try this:

DECLARE @XML XML = '<EventSchedule>
    <Event Uid="2" Type="Main Event">
        <IsFixed>True</IsFixed>
        <EventKind>MainEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
    <Event Uid="3" Type="Secondary Event">
        <IsFixed>True</IsFixed>
        <EventKind>SecondaryEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets II"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
</EventSchedule>'

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    EventIsFixed =Events.value('(IsFixed)[1]', 'varchar(20)'),
    EventKind =Events.value('(EventKind)[1]', 'varchar(20)')
FROM
 @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

Gives me an output of:

enter image description here

And of course, you can easily do an

INSERT INTO dbo.YourTable(EventUID, EventType, EventIsFixed, EventKind)
   SELECT 
         ......

to insert that data into a relational table.

Update: assuming you have your XML in files - you can use this code to load the XML file into an XML variable in SQL Server:

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK 'path-to-your-XML-file', SINGLE_BLOB) x;

and then use the above code snippet to parse the XML.

Update #2: if you need the parameters, too - use this XQuery statement:

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),
    EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
    ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
    ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
    ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
FROM
    @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

Results in:

enter image description here

edited May 30, 2013 at 16:01

answered May 30, 2013 at 14:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

9 Comments

pugu Over a year ago

That works great thanks. With this method would I use OPENROWSET, OPENXML or bulk load to get the data in from the file?

marc_s Over a year ago

@pugu: updated my response - use OPENROWSET(BULK .....) to load the file from disk

pugu Over a year ago

The problem with this method is that it doesn't include any of the parameters such as "The Muppets"...

pugu Over a year ago

That's brilliant thanks @marc_s. I'm completely new to xpath/xquery but this has helped me start getting to grips with it as well as solving my immediate problem. Off to read Books Online!

marc_s Over a year ago

@MPH: that is true - if the <Fields> node shows up multiple times, you'd have to have another CROSS APPLY Events.nodes('Fields') AS XT(XFields) or something like that

|

paul · Accepted Answer · 2013-05-30 14:50:28Z

2

You do it by creating a destination table, then a schema mapping file that maps the xml elements to table columns.

Yours might look a bit like this:

create table event (
    Type nvarchar(50),
    Name nvarchar(50),
    Duration nvarchar(50))

and this:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

   <ElementType name="Type" dt:type="string" />
   <ElementType name="Name" dt:type="string" />
   <ElementType name="Duration" dt:type="string" />

   <ElementType name="EventSchedule" sql:is-constant="1">
      <element type="Event" />
   </ElementType>

   <ElementType name="Event" sql:relation="Event">
      <element type="Type" sql:field="Type" />
      <element type="Name" sql:field="Name" />
      <element type="Duration" sql:field="Duration" />
   </ElementType>
</Schema>

Then you can load your XML into your table using the XML bulk loader.

http://support.microsoft.com/kb/316005

answered May 30, 2013 at 14:50

paul

22.1k1 gold badge56 silver badges54 bronze badges

1 Comment

zar Over a year ago

I get the error Cannot create Active X Object but related link about it doesn't seem to help. Any ideas how to fix it? I tried adding .4.0 too but still same error. But your answer is pretty old too. Is this method not used much now?

Stefan Steiger · Accepted Answer · 2015-10-21 08:03:50Z

If you need to do it without XML variable (from string in table-valued function)

SELECT 
    --myTempTable.XmlCol.value('.', 'varchar(36)') AS val 
     myTempTable.XmlCol.query('./ID').value('.', 'varchar(36)') AS ID 
    ,myTempTable.XmlCol.query('./Name').value('.', 'nvarchar(MAX)') AS Name 
    ,myTempTable.XmlCol.query('./RFC').value('.', 'nvarchar(MAX)') AS RFC 
    ,myTempTable.XmlCol.query('./Text').value('.', 'nvarchar(MAX)') AS Text 
    ,myTempTable.XmlCol.query('./Desc').value('.', 'nvarchar(MAX)') AS Description 

    --,myTempTable.XmlCol.value('(Desc)[1]', 'nvarchar(MAX)') AS DescMeth2
FROM 
(
    SELECT  
        CAST('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <data-set>
            <record>
                <ID>1</ID>
                <Name>A</Name>
                <RFC>RFC 1035[1]</RFC>
                <Text>Address record</Text>
                <Desc>Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.</Desc>
            </record>
            <record>
                <ID>2</ID>
                <Name>NS</Name>
                <RFC>RFC 1035[1]</RFC>
                <Text>Name server record</Text>
                <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
            </record>
        </data-set>
        ' AS xml) AS RawXml
) AS b 
--CROSS APPLY b.RawXml.nodes('//record/ID') myTempTable(XmlCol);
CROSS APPLY b.RawXml.nodes('//record') myTempTable(XmlCol);

Or from file:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tfu_RPT_SEL_XmlData]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[tfu_RPT_SEL_XmlData]
GO



CREATE FUNCTION [dbo].[tfu_RPT_SEL_XmlData]
(
     @in_language varchar(10) 
    ,@in_reportingDate datetime 
)
RETURNS TABLE
AS
RETURN 
(   

    SELECT 
        --myTempTable.XmlCol.value('.', 'varchar(36)') AS val 
         myTempTable.XmlCol.query('./ID').value('.', 'varchar(36)') AS ID 
        ,myTempTable.XmlCol.query('./Name').value('.', 'nvarchar(MAX)') AS Name 
        ,myTempTable.XmlCol.query('./RFC').value('.', 'nvarchar(MAX)') AS RFC 
        ,myTempTable.XmlCol.query('./Text').value('.', 'nvarchar(MAX)') AS Text 
        ,myTempTable.XmlCol.query('./Desc').value('.', 'nvarchar(MAX)') AS Description 
    FROM 
    (
        SELECT CONVERT(XML, BulkColumn) AS RawXml 
        FROM OPENROWSET(BULK 'D:\username\Desktop\MyData.xml', SINGLE_BLOB) AS MandatoryRowSetName 
    ) AS b 
    CROSS APPLY b.RawXml.nodes('//record') myTempTable(XmlCol)

)


GO


SELECT * FROM tfu_RPT_SEL_XmlData('DE', CURRENT_TIMESTAMP);

e.g.

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

So you can have a function like

SELECT * FROM MyTable 
WHERE UID IN 
(
    SELECT 
        x.XmlCol.value('.', 'varchar(36)') AS val 
    FROM 
    (
        SELECT 
        CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
    ) AS b 
    CROSS APPLY b.RawXml.nodes('e') x(XmlCol)
)

Francesco De Lisi · Accepted Answer · 2013-05-30 14:44:41Z

0

If you're trying to import your XML as a "pure" XML field you should create a table like this (obviously with many other fields as you want):

CREATE TABLE [dbo].[TableXML](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [XmlContent] [xml] NOT NULL -- specify [xml] type
)

Then you can easily insert your XML as a string:

INSERT INTO [dbo].[TableXML]
           ([XmlContent])
     VALUES
           ('<?xml version="1.0" encoding="UTF-8"?>
               <EventSchedule>
                 <Event Uid="2" Type="Main Event">
                   <IsFixed>True</IsFixed>
                   <EventKind>MainEvent</EventKind>
                   <Fields>
                     <Parameter Name="Type" Value="TV_Show"/>
                     <Parameter Name="Name" Value="The Muppets"/>
                     <Parameter Name="Duration" Value="00:30:00"/>
                   </Fields>
                </Event>
              </EventSchedule>')

Then to query start from MSDN t-SQL XML

If you prefer store it as string use a varchar(max) in place of [XML] column type and the same insert. But if you like to query easily I suggest [XML] type. With the flat string approach you need a lot of work unless you will implement some application code to parse it and store in a flat table. A good approach could be an XML storage in a "compress" TABLE and a VIEW for data retrieve with the flat field disposition.

edited May 30, 2013 at 14:44

answered May 30, 2013 at 14:31

Francesco De Lisi

1,5239 silver badges20 bronze badges

1 Comment

pugu Over a year ago

If possible I'd like to get it into a table as pure data, not XML. Or is that going to be impractical?

Ashok kumar Ganesan · Accepted Answer · 2019-10-17 11:52:39Z

How to load the below XML data into the SQL

<?xml version="1.0" encoding="utf-8"?> 
<DataTable xmlns="SmarttraceWS"> 
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> 
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="ActivityRecords" msdata:UseCurrentLocale="true"> 
      <xs:complexType> 
        <xs:choice minOccurs="0" maxOccurs="unbounded"> 
          <xs:element name="ActivityRecords"> 
            <xs:complexType> 
              <xs:sequence> 
                <xs:element name="ReferenceID" type="xs:long" minOccurs="0" /> 
                <xs:element name="IMEI" type="xs:string" minOccurs="0" /> 
                <xs:element name="Asset" type="xs:string" minOccurs="0" /> 
                <xs:element name="Driver" type="xs:string" minOccurs="0" /> 
                <xs:element name="DateTime" type="xs:string" minOccurs="0" /> 

              </xs:sequence> 
            </xs:complexType> 
          </xs:element> 
        </xs:choice> 
      </xs:complexType> 
    </xs:element> 
  </xs:schema> 
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"> 
    <DocumentElement xmlns=""> 
      <ActivityRecords diffgr:id="ActivityRecords1" msdata:rowOrder="0"> 
        <ReferenceID>2620443016</ReferenceID> 
        <IMEI>013795001360346</IMEI> 
        <Asset>L-93745</Asset> 
        <Driver>N/A</Driver> 
        <DateTime>2019-10-14 12:00:35</DateTime> 

              </ActivityRecords> 
    </DocumentElement> 
  </diffgr:diffgram> 
</DataTable>

Peter Drabik · Accepted Answer · 2023-10-13 14:13:58Z

Much faster is the use of OPENXML. Logic taken from sqlhack.com

DECLARE @xml_document xml

--read contents of XML document into a variable 
SELECT @xml_document = mycolumn
FROM OPENROWSET (BULK 'D:\Temp\yourfile.xml', SINGLE_BLOB) AS myxmldoc(mycolumn)

--associate document handle with the XML document variable
DECLARE @doc_handle int    
EXEC sp_xml_preparedocument @doc_handle OUTPUT, @xml_document

--parse document - 2 stands for element-centric mapping
SELECT
    *
FROM 
    OPENXML (@doc_handle, '/EventSchedule/Event' , 2) WITH(
        Name VARCHAR(100) 'Fields/Parameter[@Name="Name"]/@Value',
        Type VARCHAR(100) 'Fields/Parameter[@Name="Type"]/@Value',
        Duration VARCHAR(100) 'Fields/Parameter[@Name="Duration"]/@Value',
        EventKind VARCHAR(100)
    )
    
--dispose handle
EXEC sp_xml_removedocument @doc_handle

Collectives™ on Stack Overflow

Import 'xml' into Sql Server

6 Answers 6

9 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related