Using Openxml in SQL Server 2008 Stored Proc - Insert Order Differs from Xml Document

Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document

If I use the native XQuery support in SQL Server instead of the "legacy" OPENXML stuff, then it would appear that the <t> nodes are indeed inserted into the table in the order they appear in the XML document.

I've used code something like this:

INSERT INTO dbo.[Transactions]([ID], [EncryptedAccountID])
   SELECT
        XT.value('@id', 'uniqueidentifier'),
        XT.value('@encryptedAccountId', 'varchar(200)')
   FROM
      @xmlTransaction.nodes('/ts/t') AS Nodes(XT)

The same could be done for the <tv> subnodes, too.

Is using OPENXML() the best way to transform data in an XML Column to Rows and Columns?

OPENXML(), and its companions sp_xml_preparedocument/sp_xml_removedocument is a proprietary Microsoft API. It is kept just for backward
compatibility with the obsolete SQL Server 2000. SQL Server 2005 onwards supports w3c's XQuery 1.0, XPath 2.0, and XSD 1.0.

SQL

-- DDL and sample data population, start
DECLARE @tbl Table (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Values>
    <Value>Valid Value 1</Value>
    <Value>Valid Value 2</Value>
    <Value>Valid Value 3</Value>
    <Value>Valid Value 4</Value>
    <Value>Valid Value 5</Value>
    <Value>Valid Value 6</Value>
</Values>');
-- DDL and sample data population, end

SELECT ID
    , c.value('.','VARCHAR(100)') AS Result
FROM @tbl CROSS APPLY xmldata.nodes('/Values/Value/text()') AS t(c);

Output

+----+---------------+
| ID |    Result     |
+----+---------------+
|  1 | Valid Value 1 |
|  1 | Valid Value 2 |
|  1 | Valid Value 3 |
|  1 | Valid Value 4 |
|  1 | Valid Value 5 |
|  1 | Valid Value 6 |
+----+---------------+

SQL Server : insert into with openxml and timestamp

The error is because your insert is not explicitly naming the columns. The exact warning I posted in a comment a couple hours ago. This is very easy to prove.

create table Something
(
    SomeVal int
)

insert Something
select 1

select * from Something

--at this point you have 1 row in the table

--now add the new column
alter table Something add asdf timestamp

insert Something
select 1
--whoa....it failed.

--but if I name the columns it works again.
insert Something(SomeVal)
select 1

select * from Something

You might also take a look at the documentation as the timestamp datatype has been deprecated for quite some time. https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql

Parse XML via OPENXML SQL Server 2008

In order to get your own attempt running, there's nothing more than one / to much:

FROM OPENXML(@xml_doc,'/Order_Details/Order_Details',2)

But this approach is outdated. FROM OPENXML, together with the SPs to prepare and to remove a document should not be used any more (rare exceptions exist).

The modern way for XML (since v2005 :-) ) is to use the native XML methods, the XML type provides:

--Declare a table variable to hold the data in single column of XML data type
DECLARE @xml_data XML

SELECT @xml_data=
N'<Order_Details>
<Selection_ID>2100</Selection_ID>
<Order_Details>
  <Able_To_Use>Y</Able_To_Use>
  <Purchase_ID>GF-00000001</Purchase_ID>
  <QTY>1</QTY>
  <Subdetails>
    <REGION_QTY>2</REGION_QTY>
    <Testing_NO>00.05.04.01.00</Testing_NO>
  </Subdetails>
</Order_Details>
<Order_Details>
  <Able_To_Use>Y</Able_To_Use>
  <Purchase_ID>GF-00000002</Purchase_ID>
  <QTY>1</QTY>
  <Subdetails>
    <REGION_QTY>2</REGION_QTY>
    <Testing_NO>00.05.04.01.034</Testing_NO>
  </Subdetails>
</Order_Details>
</Order_Details>';

--The query

SELECT @xml_data.value('(/Order_Details/Selection_ID/text())[1]','int') AS SelectionId
      ,od.value('(Able_To_Use/text())[1]','nchar(1)') AS AbleToUse
      ,od.value('(Purchase_ID/text())[1]','nvarchar(10)') AS PurchaseId
      ,od.value('(QTY/text())[1]','int') AS Quantity
      ,od.value('(Subdetails/REGION_QTY/text())[1]','int') AS RegionQty
      ,od.value('(Subdetails/Testing_NO/text())[1]','nvarchar(10)') AS TestingNo
FROM @xml_data.nodes('/Order_Details/Order_Details') A(od)

I pick various levels here.

• The Selection_ID can be taken from the variable directly.
• We can use .nodes() to retrieve a set of repeating elements
• We can use .value() with a XPath to grab into the elements and fetch their text nodes.

The code above is the most explicit. This can be put simpler, but it is a good idea with XML to keep it as explicit as possible. Just to demonstrate, what would work doo:

This query has the same result, but it is not recommended...

SELECT @xml_data.value('(//Selection_ID)[1]','int') AS SelectionId
      ,od.value('Able_To_Use[1]','nchar(1)') AS AbleToUse
      ,od.value('Purchase_ID[1]','nvarchar(10)') AS PurchaseId
      ,od.value('QTY[1]','int') AS Quantity
      ,od.value('(Subdetails/REGION_QTY)[1]','int') AS RegionQty
      ,od.value('(Subdetails/Testing_NO)[1]','nvarchar(10)') AS TestingNo
FROM @xml_data.nodes('/Order_Details/Order_Details') A(od)

inserting a node into SQL 2008 xml datatype... checking if it exists first

An example using the value() method:

DECLARE 
  @x xml, 
  @param int

SET @x = '<root><id>1</id><id>2</id><id>3</id></root>'
SET @param = 1

IF NOT EXISTS (
  SELECT * FROM @x.nodes('/root/id') n(x) WHERE x.value('.','int') = @param
) 
PRINT 'Insert'
ELSE 
PRINT 'Return'

In SQL Server, can multiple inserts be replaced with a single insert that takes an XML parameter?

The XML should be formatted as a normal XML document would. Then you just pass it to the stored procedure using parameter type XML.

Here's an example on how to do an insert. In this case, @p_AdditionalContactInfo is the XML, and it's in this form:

<entities>
  <entity>
    <firstName>Joey</firstName>
    ...        
  </entity>
  .. more entity records
</entities>

Here's the t-sql example:

  DECLARE @l_index int

  -- parse the records from the XML
  EXECUTE sp_xml_preparedocument @l_index OUTPUT, @p_AdditionalContactInfo
  INSERT INTO @l_AdditionalContactInfoTbl
            ( ContactInfoID
            , FirstName
            , LastName 
            , ContactTypeID
            , Title
            , Email
            , AddressLine1
            , AddressLine2
            , City
            , State
            , Zip
            , MobilePhone
            , BusinessPhone
            , UpdateDateTime )
       SELECT ContactInfoID
            , FirstName
            , LastName
            , ContactTypeID
            , Title
            , Email
            , AddressLine1
            , AddressLine2
            , City
            , State
            , Zip
            , MobilePhone
            , BusinessPhone
            , UpdateDateTime
         FROM OPENXML (@l_index, 'entities/entity', 1)
              WITH (  ContactInfoID  int          'id'
                    , FirstName      varchar(50)  'firstName'
                    , LastName       varchar(50)  'lastName'
                    , ContactTypeID  int          'contactTypeId'
                    , Title          varchar(20)  'title'
                    , Email          varchar(100) 'email'
                    , AddressLine1   varchar(100) 'addressLine1'
                    , AddressLine2   varchar(100) 'addressLine2'
                    , City           varchar(50)  'city'
                    , State          varchar(2)   'state'
                    , Zip            varchar(5)   'zip'
                    , MobilePhone    varchar(12)  'mobilePhone'
                    , BusinessPhone  varchar(12)  'businessPhone'
                    , UpdateDateTime datetime     'updateDateTime'
                   )
  EXECUTE sp_xml_removedocument @l_index

Getting XML to feed into SQL Server table

Here is adjusted working SQL. Just uncomment the INSERT lines when you are ready.

SQL

WITH XmlFile (xmlData) AS
(
   SELECT CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK 'C:\Users\cj\Documents\EodBalance.xml', SINGLE_BLOB) AS x
)
--INSERT INTO [dbo].[EndofDay] 
--([PouchID], [Lane], [BusinessDay], BusinessStartingTime, BusinessEndingTime, [StartingBalance], [EndingBalance])
SELECT c.value('(PouchId/text())[1]', 'VARCHAR(25)') AS [PouchId]
   , c.value('(Lane/text())[1]', 'NCHAR(2)') AS [Lane]
   , c.value('(BusinessDay/text())[1]', 'DATE') AS [BusinessDay]
   , c.value('(BusinessStartingTime)[1]', 'datetime') AS [BusinessStartingTime]
   , c.value('(BusinessEndingTime/text())[1]', 'datetime') AS [BusinessEndingTime]
   , c.value('(StartingBalance/text())[1]', 'MONEY') AS [StartingBalance]
   , c.value('(EndingBalance/text())[1]', 'MONEY') AS [EndingBalance]
FROM XmlFile CROSS APPLY xmlData.nodes('/ArrayOfSafeEODBalance/SafeEODBalance') AS t(c);

Have XML file need it to populatemultiple SQL Tables

Assuming you're using SQL Server, you should parse the XML into DataTables and use the SqlBulkCopy object to shoot them into the database super-fast. There are lots of resources to help you learn about SqlBulkCopy. Here's a recent discussion from another StackOverflow question to get you started: Sql Server 2008 Tuning with large transactions (700k+ rows/transaction)

If the XML file is really large, you should be careful what sort of parser you use. XDocument and XmlDocument load the whole thing into memory. If the files are small enough, say under 10MB, you should be fine using those parsers.

---

EDIT:

Here's a quick mock-up of how you could get the XML into DataTables. It's in VB since VB makes XML a tad easier.

Option Strict On : Option Explicit On : Option Infer On : Option Compare Binary

Imports System.Data
Imports System.Linq
Imports System.Xml.Linq

Module Module1

    Sub Main()
      Dim xml =
         <Trip>
            <TripDetails id="1">
               <DepartureDate/> 
               <ReturnDate/> 
               <TripTypeA>3</TripTypeA> 
               <TripTypeB>1</TripTypeB> 
               <PurposeOfTrip>vacation</PurposeOfTrip> 
               <Region>5</Region> 
               <Countries>
                  <Country>105</Country> 
                  <Country>135</Country> 
               </Countries>
               <Cities>
                  <City>Cancun</City> 
                  <City>Tokyo</City> 
                  <City>Mayo</City> 
               </Cities>
               <OverallRating>4</OverallRating> 
               <Suppliers>53</Suppliers> 
               <SuppliersComments>Good flight</SuppliersComments> 
               <Transport>
                  <TransportType>1</TransportType> 
                  <TransportType>3</TransportType> 
               </Transport>
               <TransportComment>Transportation was fast</TransportComment>
            </TripDetails>
         </Trip>

         Dim dtTripDetails As New DataTable()
         With dtTripDetails.Columns
            .Add("TripID", GetType(Integer))
            .Add("TripTypeA", GetType(Integer))
            .Add("DepartureDate", GetType(DateTime))
            .Add("TransportComment", GetType(String))
         End With

         Dim dtTripDetailXrefCountries As New DataTable()
         With dtTripDetailXrefCountries.Columns
            .Add("TripID", GetType(Integer))
            .Add("CountryID", GetType(Integer))
         End With

         Dim xdetails = From td In xml.Descendants("TripDetails") Select td
         For Each xdetailRecord As XElement In xdetails
            Dim tripID As Integer = CInt(xdetailRecord.Attribute("id").Value)
            Dim tripTypeA As Integer = CInt(xdetailRecord.Element("TripTypeA").Value)
            Dim strDepDate As String = xdetailRecord.Element("DepartureDate").Value
            Dim depDate As Object = If(String.IsNullOrEmpty(strDepDate), CType(DBNull.Value, Object), CType(DateTime.Parse(strDepDate), Object))
            Dim transportComment As String = xdetailRecord.Element("TransportComment").Value
            dtTripDetails.Rows.Add(tripID, tripTypeA, depDate, transportComment)

            Dim xcountries = From c In xdetailRecord.Element("Countries").Elements("Country") Select c
            For Each xcountryRecord As XElement In xcountries
               Dim countryID As Integer = CInt(xcountryRecord.Value)
               dtTripDetailXrefCountries.Rows.Add(tripID, countryID)
            Next
         Next

         Console.WriteLine("Done")
         Console.ReadKey(True)

    End Sub

End Module

BTW - when doing this kind of ETL, it's best to pump your data into staging tables first rather than directly into your production tables. That way, you can validate data types and ensure referential integrity and handle key management and get everything perfectly situated without locking up or polluting your production tables.

---

Related Topics