Advertisements
Home > XML > Save XML in a file | Read XML from a file (in SQL Server)

Save XML in a file | Read XML from a file (in SQL Server)


USE [AdventureWorks]
GO

-- Save XML records to a file:
DECLARE @fileName VARCHAR(50)

DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

SET @fileName = 'E:\SQL_Queries\test.xml'
SET @sqlStr = 'select TOP 50 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] from AdventureWorks.Person.Contact FOR XML PATH(''Contact''), ROOT(''Person.Contact'')'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'

EXEC xp_cmdshell @sqlCmd
Progress:-
NULL
Starting copy...
NULL
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (400.00 rows per sec.)
NULL

Check the file in the directory set above in @filename variable.

Now we will see how can we read the XML file saved above. Will store the XML in into a SQL Server XML variable
and query it with SELECT statement:

DECLARE @xmlStr XML

-- Storing XML records into a XML variable:

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_CLOB) AS X

The above statement gave me ERROR:
SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.
Because the XML BCP’d to the file above was saved in unicode format. So use SINGLE_NCLOB instead of SINGLE_CLOB.

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

This again gave me ERROR:
XML parsing: line 1, character 2034, illegal qualified name character
Because the file saved in xml file has CrLf (Carriage Return & Line Feed) in between.
So I removed the CrLf from the XML string as shown below.

SELECT @xmlStr = REPLACE(REPLACE(BulkColumn,CHAR(10),''),CHAR(13),'')
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

-- Querying the XML stored in @xmlStr variable with SELECT statement:
SELECT
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
FROM @xmlStr.nodes('//Person.Contact/Contact') as T(C)
Advertisements
  1. Sarah
    January 14, 2014 at 6:50 pm

    Can you show me how I can do an update rather than an insert?

  2. NEELAKANDAN
    March 20, 2013 at 1:45 pm

    ‘hello’

    how to validate above xml is valid or not

  3. deepkt
    November 1, 2011 at 12:57 pm

    Header like
    ?xml version=”1.0″ ?

  4. November 1, 2011 at 12:18 pm

    What headers you are talking about, can you provide an example?

  5. deepkt
    November 1, 2011 at 11:26 am

    Hi,

    While saving XML in a file ,how can we generate with XML header
    ?

  1. October 23, 2015 at 3:55 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: