Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Scripts to use XML to insert and update rows in a SQL Server table


By:   |   Last Updated: 2010-09-24   |   Comments (21)   |   Related Tips: 1 | 2 | 3 | 4 | More > XML

Problem

Over the years I have had to adapt to many issues presented before me as a Database Administrator and I have gathered many scripts to deal with them. One that has become quite a topic is XML integration for transactions to the database. In developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is what I used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can be updated. The following article breaks down this process from beginning to end.

Solution

First, we need to deal with what data is in the transaction and the format it needs to conform to. For this example, I will be using the pubs database and work with transactions to authors.

Basically, the correctly formatted XML is generated with the table name (authors) as the parent node, rows are delimited by and the fields are the child nodes, as shown below.

<BOOKINFO>
 <AUTHORS>
     <ROW>
       <AU_ID>172-32-1176</AU_ID>
       <AU_LNAME>White</AU_LNAME>
       <AU_FNAME>Johnson</AU_FNAME>
       <PHONE>408 496-7223</PHONE>
       <ADDRESS>10932 Bigge Rd.</ADDRESS>
       <CITY>Menlo Park</CITY>
       <STATE>CA</STATE>
       <ZIP>94025</ZIP>
       <CONTRACT>1</CONTRACT>
     </ROW>
 </AUTHORS>
</BOOKINFO>

Note: XML is case-sensitive therefore whatever you have for table names and column names must exactly match the XML.

For those wondering how this is done auto-magically, this select query will generate a sample XML document from the authors table in the pubs database:

SELECT 
( 
SELECT
( 
SELECT au.*
FROM authors au 
WHERE au_lname = 'White' 
FOR XML PATH('Row'), ELEMENTS, TYPE 
) FOR XML PATH ('authors'), TYPE 
) "*" FOR XML PATH('BookInfo') 

Now that we know what we are working with, we can move onto processing the transaction.

I have two processes one for inserting data and another for updating data. I will walk through the Insert and Update processes, so you have a better understanding of how the stored procedures work.


INSERT

In this section, I will walk through the different processes within the Insert stored procedure, which are to extract the NodePath and Table names from the XML, retrieve the schema datatypes, primary keys for that table, prepare the statement and then lastly loop through the XML to perform the request. You can download all of the code for this tip.

The following breaks apart the INSERT stored procedure and I'll explain each step in the process.

This is the first part of the procedure.

-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for INSERT
-- Dependencies: [dbo].[fnGetTableSchemaInsert], [dbo].[fnSetTableSchemaSelect], 
[dbo].[fnGetTableSchemaSelectInto],[dbo].[fnIsDate]
-- =============================================
CREATE PROCEDURE [dbo].[prXMLDataInsert] 
(
@XmlData XML
)
AS
BEGIN
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @hdoc INT
-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData 

After testing several XML methods, the quickest way I found was to use the sp_xml_preparedocument then insert the results into a temporary dataset in order to quickly query values from the XML to process.

-- Set Raw XML Schema
SELECT *
INTO #xmlDoc 
FROM OPENXML( @hdoc, '//*',2)

Once we have the dataset, our example XML data would appear as below:

inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data

The next step is to get the NodePath and table names from our temp dataset by using the query below:

-- Set Primary Table to use
SELECT DISTINCT IDENTITY(INT,1,1) id, rt.localname + '/' + tbl.localname + '/' 
+ col.localname AS NodePath, tbl.localname AS NodeRow
INTO #xml
FROM #xmlDoc rt 
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID AND rt.parentID IS NULL
INNER JOIN #xmlDoc col
ON tbl.id = col.parented

The results of the query would then appear as follows:

get the NodePath and table names from our temp dataset

At this point, we are ready to loop through the XML data as illustrated below. A few points of interest, I used a while loop verses a cursor to keep down the resource overhead and keep the speed up, and for those that haven't learned the hard way with sp_executesql since we have to pass in the XML into the statement, we only have nvarchar(4000) to store the statement, which causes chaos when you have tables with many columns.

DECLARE @i INT, @NodePath VARCHAR(255), @NodeRow VARCHAR(50),
@NodeKeys VARCHAR(255), @NodeCol VARCHAR(2000), 
@UpdateNodes VARCHAR(2000), @sSql NVARCHAR(4000), 
@SetSchemaSelect VARCHAR(4000), @iVars VARCHAR(2000)
-- Set id of first row
SELECT @i = MIN(id) FROM #xml
-- Begin looping through xml recordset
WHILE @i IS NOT NULL
BEGIN 
SELECT @NodePath = NodePath, @NodeRow = NodeRow FROM #xml WHERE id = @i 

What I found in order to build out the statements correctly, we need to get the column schema for the table were inserting into, but not any auto increment keys. Build the SELECT statement for the INTO portion of the statement and our OPENXML WITH fields with the correct datatypes, using the following functions illustrated below.

-- Get Table Schema for XML data columns
SELECT @NodeCol =[dbo].[fnGetTableSchemaInsert](@NodeRow)
SELECT @SetSchemaSelect = [dbo].[fnSetTableSchemaSelect](@NodeRow)
SELECT @ivars = [dbo].[fnGetTableSchemaSelectInto](@NodeRow)
DECLARE @param NVARCHAR(50), @pkID INT, @pkIDOUT INT
SET @param = N'@hdoc INT, @pkIDOUT INT OUTPUT'

For our sp_executesql we need to SET the @hdoc and can pass the identity value back if there is one for the table. As we continue, we have set our INSERT variables and now can build our final statement to execute.

-- This updates xml Recordset on primary keys of a given table 
SET @sSql = 'INSERT INTO ' + @NodeRow + '(' + @iVars + ') SELECT ' + @SetSchemaSelect + 
' FROM OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' + @NodeCol + ') as xm 
SELECT @pkIDOUT = SCOPE_IDENTITY()'

A sample of the final statement generated from the above line of code would appear as:

INSERT INTO authors(au_id,au_lname,au_fname,phone,address,city,state,zip,contract) 
SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract] 
FROM OPENXML( @hdoc, 'BookInfo/authors/Row',2) WITH ([au_id] VARCHAR(11), [au_lname] VARCHAR(40), 
[au_fname] VARCHAR(20), [phone] CHAR(12), [address] VARCHAR(40), [city] VARCHAR(20), [state] CHAR(2), 
[zip] CHAR(5), [contract] bit) AS xm 
SELECT @pkIDOUT = SCOPE_IDENTITY() 

Note: Here is where we can get in trouble with the nvarchar(4000) limit when the statement is dynamically assembled.

/******* Execute the query and pass in the @hdoc for update *******/
EXEC sp_executesql @sSql, @param, @hdoc, @[email protected] OUTPUT
/***** Movenext *****/
SELECT @i = MIN(id) FROM #xml WHERE id > @I
END 
-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml
END

UPDATE

Well I have explained the XML INSERT, now to we can step through the UPDATE process, which fundamentally goes through the same steps as the INSERT, but the functions for this stored procedure generate update values for the sp_executesql loop.

-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for UPDATE
-- Dependencies: [dbo].[fnGetTableSchema], [dbo].[fnGetTableUpdate],
-- [dbo].[fnGetPrimaryKeys],[dbo].[fnIsDate], [dbo].[fnGetTableKeys]
-- =============================================
ALTER PROCEDURE [dbo].[prXMLDataUpdate] 
(
@XmlData XML
)
AS
BEGIN
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @hdoc INT
-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData
-- Set Raw XML Schema
SELECT * 
INTO #xmlDoc 
FROM OPENXML( @hdoc, '//*',2)
-- Set Primary Table to use
SELECT DISTINCT IDENTITY(INT,1,1) id, rt.localname + '/' + tbl.localname + '/' 
+ col.localname AS NodePath, tbl.localname AS NodeRow
INTO #xml
FROM #xmlDoc rt 
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID AND rt.parentID IS NULL
INNER JOIN #xmlDoc col
ON tbl.id = col.parented
DECLARE @i INT, @NodePath VARCHAR(255), @NodeRow VARCHAR(50), @NodeKeys VARCHAR(255), 
@NodeCol VARCHAR(4000), @UpdateNodes VARCHAR(4000), @sSql NVARCHAR;
--Set id of first row
SELECT @i = MIN(id) FROM #xml;
-- Begin looping through XML recordset to check for exists
WHILE @i IS NOT NULL
BEGIN 
SELECT @NodePath = NodePath, @NodeRow = NodeRow 
FROM #xml 
WHERE id = @i 
-- Get Table Schema for XML data columns
SELECT @NodeCol = [dbo].[fnGetTableSchema](@NodeRow)
SELECT @UpdateNodes =[dbo].[fnGetTableUpdate](@NodeRow)
SELECT @NodeKeys = [dbo].[fnGetPrimaryKeys](@NodeRow) 

Just as the INSERT our sp_executesql will need to SET the @hdoc parameter and optionally pass the identity value back if there is one for the table.

DECLARE @param NVARCHAR(50) 
SET @param = N'@hdoc INT' 

At this point, we have set our UPDATE variables and can now build our final statement to execute.

SET @sSql = 'UPDATE ' + @NodeRow + ' SET ' + @UpdateNodes + ' FROM 
OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' + 
@NodeCol + ') as xm INNER JOIN ' + @NodeRow + ' ON ' +
@NodeKeys

A sample of the final statement generated would appear as:

UPDATE authors SET [au_lname] = xm.au_lname, 
[au_fname] = xm.au_fname, 
[phone] = xm.phone, 
[address] = xm.address, 
[city] = xm.city, 
[state] = xm.state, 
[zip] = xm.zip, 
[contract] = xm.contract 
FROM OPENXML( @hdoc, 'BookInfo/authors/Row',2) WITH ([au_id] VARCHAR(11), 
[au_lname] VARCHAR(40), [au_fname] VARCHAR(20), [phone] CHAR(12), 
[address] VARCHAR(40), [city] VARCHAR(20), [state] CHAR(2), 
[zip] CHAR(5), [contract] bit) AS xm 
INNER JOIN authors 
 ON authors.au_id=xm.au_id

Note: Let me pass a few words of wisdom concerning the OPENXML and WITH function. First the XML field values are all for practical purposes varchar, which is why we declare the Datatypes in the WITH section. When we have a datetime value to process, a value of NULL is translated to 1900-01-01 00:00:00.000, which is why I created the fnIsDate() function to turn it back to NULL.

-- Execute the query and pass in the @hdoc for UPDATE
EXEC sp_executesql @sSql, @param, @hdoc

/***** Movenext *****/
SELECT @i = MIN(id) FROM #xml WHERE id > @I
END 

Finally, need to insure that we release the variables before returning

-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml
END 

Putting It Together

Now that we have walked through the code for the Insert and Update these are functions that are required and are all part of the code download.

Functions:

  • [dbo].[fnGetTableSchemaInsert]
  • [dbo].[fnSetTableSchemaSelect]
  • [dbo].[fnGetTableSchemaSelectInto]
  • [dbo].[fnIsDate]
  • [dbo].[fnGetTableSchema]
  • [dbo].[fnGetTableUpdate]
  • [dbo].[fnGetPrimaryKeys]
  • [dbo].[fnGetTableKeys]

Stored Procedures:

  • prXMLDataInsert
  • prXMLDataUpdate

Examples

After you have created all of these objects in your database (in our case the pubs database) we can then use them to create new records or update existing records.

In this first example I have modified the fname to be "John" and the lname to be "Doe".

SELECT * FROM authors WHERE au_id = '172-32-1176';

EXEC dbo.prXMLDataUpdate 
'
<BOOKINFO>
 <AUTHORS>
     <ROW>
       <AU_ID>172-32-1176</AU_ID>
       <AU_LNAME>Doe</AU_LNAME>
       <AU_FNAME>John</AU_FNAME>
       <PHONE>408 496-7223</PHONE>
       <ADDRESS>10932 Bigge Rd.</ADDRESS>
       <CITY>Menlo Park</CITY>
       <STATE>CA</STATE>
       <ZIP>94025</ZIP>
       <CONTRACT>1</CONTRACT>
     </ROW>
 </AUTHORS>
</BOOKINFO>';

SELECT * FROM authors WHERE au_id = '172-32-1176'; 

Here are the before and after values in the table.

use them to create new records or update existing records.

In this example we will create a new author with an ID = '999-99-9999'

SELECT * FROM authors WHERE au_id = '172-32-1176';

EXEC dbo.prXMLDataUpdate 
'
<BOOKINFO>
 <AUTHORS>
     <ROW>
       <AU_ID>999-99-9999</AU_ID>
       <AU_LNAME>Doe</AU_LNAME>
       <AU_FNAME>John</AU_FNAME>
       <PHONE>408 496-7223</PHONE>
       <ADDRESS>10932 Bigge Rd.</ADDRESS>
       <CITY>Menlo Park</CITY>
       <STATE>CA</STATE>
       <ZIP>94025</ZIP>
       <CONTRACT>1</CONTRACT>
     </ROW>
 </AUTHORS>
</BOOKINFO>';

SELECT * FROM authors WHERE au_id = '999-99-9999'; 

Here are the before and after values in the table.

In this example we will create a new author with an ID = 999-99-9999

Next Steps
  • Remember XML is case-sensitive so any table name, column name, procedure or parameter must be exactly the same as the XML your trying to submit in the transaction.
  • sp_executeSQL is a good function, but has a nvarchar(4000) character limit for the statement, so building INSERT or UPDATE queries dynamically can truncate with tables that have many columns and fail to execute.
  • You can add a return dataset or pass the scope-identity on INSERT
  • This same process can be applied to executing store procedures instead of direct INSERT/UPDATES of tables. Same rules apply with case-sensitive procedure names and parameters.
  • Finally, beware the XML datetime datatype, you will bang your head off the keyboard trying trouble shoot, like I did.
  • Download the code. If formatting is hard to read, right click and do a Save As to save the file locally.


Last Updated: 2010-09-24


next webcast button


next tip button



About the author
MSSQLTips author Wayne Ackman Wayne Ackman has held the position of Senior Database Administrator with various corporations using SQL Server over the last two decades.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 28, 2018 - 10:19:31 AM - Harendra Back To Top

 Dear Sir,

Your last statement, "Finally, beware the XML datetime datatype, you will bang your head off the keyboard trying trouble shoot" really made big headache for me. 

 

I heartly request to provide the solution.

 

Harendra

 


Monday, October 17, 2016 - 7:22:04 AM - Vijay Bhabhor Back To Top

 Hi,

Please check our Case

1) We are generating one XML stock file from ERP

2) File is Updated on PHP server

But when second time we run the file the stock qty is not updated. What will be the possible solution.

XML file is around 30 MB and it taking almost 30 minutes, is something we are doing wrong?

 

Best Regards

Vijay bhabhor

vijaybhabhor.com


Thursday, July 21, 2016 - 10:20:57 AM - Michael G Back To Top

 Hi,

I suggest an improvement [fnGetPrimaryKeys] is order to get only the primary key and not the unique keys

SELECT @sSql = @sSql + @TableName + '.' + Column_Name + '=xm.' + Column_Name + ' AND '
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND table_name = @TableName
AND Ordinal_position = @i

Hope it will help other people, but thank you very much for this article.

 


Tuesday, March 08, 2016 - 5:29:01 AM - panneer Selvam Back To Top

 Thank a lot for this post. this is very useful for me to complete my task.

 


Tuesday, December 01, 2015 - 4:35:27 AM - Atang M Back To Top

Please can you assist on this. I keep on getting error"Msg 208, Level 16, State 1, Line 2 Invalid object name 'narrative'.

I need to change values from "True" to False for few specific rowID's. Below is my syntax.

--Select retrieve right results--

Select 'xxxxx....'cardNumber,'00000....'idnumber,n.r.value('.[1]','varchar(255)') AS [Comunication T/F],CAST(narrative as xml) AS narrative

From (SELECT CAST(narrative as xml) AS narrative,Cardnumber,creationDate,idNumber FROM tbl#(nolock))c

OUTER APPLY narrative.nodes('Narrative/customNarrative/dishNarrative/receiveCommunication') AS n(r)

Where  c.idNumber ='8906175000089'

--Results--

cardNumber IDnumberComunication T/F narrative

xxxxx....890617....           true          <Narrative><customNarrative><dishNarrative>..

--Herewith my syntax in question--

Begin Tran

update tbl#set

    Narrative.modify('replace value of (/dishNarrative/@receiveCommunication)[3]with "false"')

where id = 214

Commit

Regards,

Atang


Wednesday, August 05, 2015 - 10:14:04 AM - ANIL KUMAR DUBEY Back To Top

with resultset as(select convert(time,'1:20') time union all select convert(time,'1:20')union all select convert(time,'1:20')union all select convert(time,'1:20'))

select convert(time,replace(sum(datepart(hh,time))+convert(numeric(10,2),sum(datepart(mi,time))/ 60 + (sum(datepart(mi,time)) % 60) / 100.0),'.',':')) from resultset


Thursday, November 07, 2013 - 9:34:49 AM - Nikos Andranakis Back To Top

Good afternoon

if i try to run prXMLDataInsert i get follwing

How can i solve this?

thanks in advance

Nikos

INSERT INTO AUTHORS(//) SELECT // FROM OPENXML( @hdoc, 'BOOKINFO/AUTHORS/ROW',2) WITH (//) as xm SELECT @pkIDOUT = SCOPE_IDENTITY()
Incorrect syntax near '/'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


Wednesday, September 25, 2013 - 3:16:39 PM - Jorge Back To Top

Thank you but I found the issue.  It was trying to convert to decimal in the sql statement within the WITH statement.  I add decimal to the line of types in the case statement inside of the function fnGetTableSchemaInsert. 


Wednesday, September 25, 2013 - 10:11:24 AM - Jorge Back To Top

I am running into an issue when trying to insert decimals into the table from the XML file.  Currently it drops anything after the decimal place and only returns the whole numbers of the decimal.  The table shows the correct number of decimal places after the decimal.  Am I missing where you convert varchar to integer somewhere and hence lose my precision?  Thanks Jorge


Monday, August 12, 2013 - 4:20:29 PM - Wayne Ackman Back To Top

Somil,
Appears some of my text didnt paste correctly, think it might be the quotes:

SET @xmldata = <?xml version="1.0"?>
 <employeedetails>
  <employeeid>1</employeeid>
  <employeename>somil</employeename>
 </employeedetails>


Monday, August 12, 2013 - 4:00:48 PM - Wayne Ackman Back To Top

Somil,
Have you just tried this instead:

DROP TABLE ProductStore
DROP XML SCHEMA COLLECTION productionemployee1

CREATE TABLE ProductStore
(
 productid int,
 productname xml--(productionemployee1)
)

DECLARE @xmlData as xml

SET @xmlData = '
 
  1
  somil
 '

INSERT INTO productstore VALUES(1,@xmlData)

 


Friday, August 09, 2013 - 9:10:41 AM - somil Back To Top

create xml schema collection productionemployee1
as
'<xsd:schema targetNamespace="http://www.jpadtechnologies.com"
    xmlns="http://www.jpadtechnologies.com"
    elementFormDefault="qualified"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
    <xsd:element name="employeedetails">
       <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="employeeid" type="xsd:int"/>
                <xsd:element name="employeename" type="xsd:string"/>
            </xsd:sequence>
       </xsd:complexType>
    </xsd:element>
    </xsd:schema>'
 create table productstore
(
productid int,
productname xml(productionemployee1)
)

insert into productstore values(1,'<?xml version="1.0"?><
xmlns="http://www.jpadtechnologies.com">
<employeedetails><employeeid> 1</employeeid> <employeename>somil<employeename></employeedetails>')

i am trying to insert value but getting error....please help(all are working fine only insert command is producing error in xml collection schema....


Thursday, October 07, 2010 - 11:59:42 AM - Jay Back To Top
ah yes.  dynamic was not something I had to worry about.


Thursday, October 07, 2010 - 10:31:30 AM - Wayne Ackman Back To Top

R.P.Rozema wrote – “What if the value in a datetime column is actually 1900-01-01 00:00:00.000? Your code will generate a NULL value for it instead of the date value.”

 

Reply: The issue I had to handle with our situation was when declaring an incoming XML column as a datetime field and there was no date, the sp_xml_preparedocument defaulted the data to  1900-01-01, which through the validation off when checking if the end-user has entered the date yet in proofing, so I forced it to NULL in this case.

 

“Other than you I use the .nodes() function instead of the openxml method.”

 

Reply: I also like this technique, unfortunately in our scenario, working with the developers; the scope requirement was to stay object oriented and dynamic not hard-code declared schema values or when building out the statement, it was easier to predict the datatypes and create the statement. In this way, should columns change in the future on a given table I do not have to remember to modify the xml procedures.

 

“Have you already encountered the problem that you cannot generate an xml for all character values? We have in our database some records in which an end user managed to get a character with ascii code 2 into a varchar(n) column. Now if you try to generate an xml from that particular record using the for xml method, you'll always get an error message about character 0x02 not being a valid xml character. Not even its numeric character reference (NCR) &0x02; is allowed. The same thing holds for all characters under ascii 32, except for the tab, newline and linefeed characters. They can not be put in an xml as NCR's.”

 

Reply: I have not run across this issue yet, but I will investigate it further because eventually the users will try it and it will come across the helpdesk.

 

Jay Wrote – “I just change the table variable definition, the INSERT, SELECT and WITH lines and put this at the top of my procedure.  Now my data is in a table variable or temp table if need be which is used later on in the stored procedure.

 

Reply: With the project scope, predicting the many tables that might be updated or inserted, I had to be dynamic enough to script the variations and would be impossible to declare columns and data types. Although I prefer using Declared table variables verses the temp table because they are disposed of when the script is finished, unlike the temp table which the server cleans up every so often.


Thursday, October 07, 2010 - 3:07:33 AM - R.P.Rozema Back To Top
Your method does not properly handle null values. What if the value in a datetime column is actually 1900-01-01 00:00:00.000? Your code will generate a NULL value for it instead of the date value. Other than you I use the .nodes() function instead of the openxml method. I don't have the time to test if this works with openxml the same way it does with .nodes(), but I still want to give you this tip: I first declare the schema-instance using the "with xmlnamespaces( 'http://www.w3.org/2001/XMLSchema-instance' as xsi)" and then add in the column's xpath clause a filter condition on the xsi:nil attribute. This way the .value() function returns null only if the original column contained a null value. An example:

 

 

declare @xml xml;

 

select @xml = (

select 

t.name, 

t.date

from (

select 'Bond, James' as name, null as date

union all select 'Old dude' as name, {d '1900-01-01'} as date

union all select suser_name() as name, getdate() as date

) t

for xml path('field'), root('fields'), type, elements xsinil

);

 

with xmlnamespaces( 'http://www.w3.org/2001/XMLSchema-instance' as xsi)

select

fields.f.value('name[1][not(@xsi:nil = "true")]', 'sysname'),

fields.f.value('date[1][not(@xsi:nil = "true")]', 'datetime')

from @xml.nodes('/fields/field') fields(f);

 

 

I have submitted an article on this subject over on SQLservercentral, but Steve hasn't published it yet.

 


Thursday, October 07, 2010 - 2:17:54 AM - R.P.Rozema Back To Top
Have you already encountered the problem that you can not generate an xml for all character values? And if so how did you work around it? We have in our database some records in which an end user managed to get a character with ascii code 2 into a varchar(n) column. Now if you try to generate an xml from that particular record using the for xml method, you'll always get an error message about character 0x02 not being a valid xml character. Not even its numeric character reference (NCR) &0x02; is allowed. The same thing holds for all characters under ascii 32, except for the tab, newline and linefeed characters. They can not be put in an xml as NCR's.

I've contacted MS on this through Connect and they stated it's by the definition of the w3c for xml that these characters' NCR's can not be included in xml. I think they misinterpreted the definition. Please vote on connect to have MS lift this restriction or at least give us an option to enable these NCR's for these characters in xml: https://connect.microsoft.com/SQLServer/feedback/details/547817/xml-type-does-not-accept-all-character-escape-sequences


Wednesday, October 06, 2010 - 4:37:42 PM - admin Back To Top
Thanks for pointing out the issue with the &#160;.  I thought I removed all of them.  These are now cleaned up.


Wednesday, October 06, 2010 - 3:57:22 PM - Jay Back To Top
By the way, this '&#160;' is scattered in a few places throught the article.  I'm betting it's some sort of text to html rendering problem.  Anyway, I thought I'd share my while loop snippet as well.  I use this so that I don't have to have two selects.

DECLARE @ptr INT;
SET @ptr = -1;
WHILE @ptr IS NOT NULL
BEGIN
    SELECT @ptr = MIN (id)
    FROM @tblTemp01
    WHERE id > @ptr;

    IF (@ptr IS NOT NULL)
    BEGIN
          -- Do Stuff
    END
END


Wednesday, October 06, 2010 - 3:46:35 PM - Jay Back To Top
Interesting...

I've been using this snippet for my stuff.  Usually, the UI is underdevelopment at the same time so I can set my own specifications on the xml format.  I just change the table variable definition, the INSERT, SELECT and WITH lines and put this at the top of my procedure.  Now my data is in a table variable or temp table if need be which is used later on in the stored procedure.

 

-- our xml document 
    DECLARE @doc XML;
    SET @doc = '<root>
                    <parameter id="1003011102" myType="1" myOption="Red" />
                    <parameter id="1003011101" myType="2" />
                    <parameter id="1003011100" myType="3" />
                </root>';
    --SET @doc = @xmlDoc;

-- xml table handl
    DECLARE @hdoc INT;   

----declare table variable to hold our data
    DECLARE @tblXML TABLE (
        id INT PRIMARY KEY
        , myType INT
        , myOption VARCHAR(50)
    )

--Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

-- query the xml for data
    INSERT @tblXML(id, myType, myOption)
    SELECT id, myType, myOption
    FROM OPENXML (@hdoc, 'root/parameter')
        WITH (id INT, myType INT, myOption VARCHAR(50));

-- Remove the internal representation.
    EXEC sp_xml_removedocument @hdoc

SELECT * FROM @tblXML


Friday, September 24, 2010 - 9:36:20 AM - Greg Back To Top
This is not really the place to post this question, but here is a possible solution.  It is not very straight forward becuase we need to parse out the hour and minutes and calculate based on that. 

select cast(floor( ( (sum(FLOOR(time1)) * 60) + (sum(time1 - floor(time1)) * 100) ) /60) as decimal(18,2)) +
round((( (sum(FLOOR(time1)) * 60) + (sum(time1 - floor(time1)) * 100) ) /60 -
cast(floor( ( (sum(FLOOR(time1)) * 60) + (sum(time1 - floor(time1)) * 100) ) /60) as decimal(18,2)) ) * 60, 0,0) / 100
from dbo.Table_1


Friday, September 24, 2010 - 8:01:48 AM - Venkat919 Back To Top
I have data as fallows in my database.

col1     col2     col3   col4

1.20    1.20    1.20   1.20

when I am calculating this by using Sum Function it is giving result as 4.80

but I want re*** as 5.20. my requrement is to calculate as a time actually 1.20 is 1hr 20mins

so please advice how to do this


Learn more about SQL Server tools