Archive

Posts Tagged ‘XML’

Export XML column data to a file.xml

April 13, 2015 5 comments

There are times when you want to export XML data stored in Table’s columns to xml files for some purpose.

Here is a small hands on script that uses BCP command with QueryOut option to export data from SQL Server to file system:

USE [AdventureWorks2012]
GO

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

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

SET @fileName = 'D:\SQL_Queries\PersonAdditionalContactInfo.xml'
SET @sqlStr = 'select TOP 1 AdditionalContactInfo from AdventureWorks2012.Person.Person where AdditionalContactInfo IS NOT NULL'

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

EXEC xp_cmdshell @sqlCmd

Output:

NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 140    Average : (7.14 rows per sec.)
NULL

If you have to extract XMLs stored in all the rows then you can iterate through all the rows one by one by using a WHILE loop or a CURSOR and extract data in separate files. You can provide unique names to your files dynamically by tweaking the above code.

Categories: XML Tags: , , , ,

MSDN & Online Technical Articles on using XML with SQL

December 22, 2011 Leave a comment

On this post I’m not going to discuss anything about XML. Here I’m listing out some links that I found very informative so that I can refer to them easily in future, and so can you.

My all previous posts on XML-SQL can be found at: https://sqlwithmanoj.wordpress.com/category/xml/

 

There are also lot of Technical Articles in MS BOL that discuss about using XML in SQL Server.

Here is a list of those:

What’s New in FOR XML: http://msdn.microsoft.com/en-US/library/ms345137(v=SQL.90).aspx

XML Best Practices: http://msdn.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx

XML Indexes: http://msdn.microsoft.com/en-US/library/ms345121(v=SQL.90).aspx

XML Options: http://msdn.microsoft.com/en-US/library/ms345110(v=SQL.90).aspx

XML Support: http://msdn.microsoft.com/en-US/library/ms345117(v=SQL.90).aspx

Performance Optimizations for the XML Data Type: http://msdn.microsoft.com/en-US/library/ms345118(v=SQL.90).aspx

… enjoy reading.

 

Please post your comments and links if you found any informative online article about XML-SQL.

Categories: Datatypes, XML Tags: , , ,

Query XML with different languages or different UNICODE encodings [UTF-8 & UTF-16]

December 9, 2011 1 comment

While creating XML documents we should take care of the UTF encoding if we are dealing with NON-US or international data. Non-English foreign languages fall in different ranges and takes more memory size compared to English language and thus require different encoding schemes, as follows:

>> UTF-8: represents an octet (8 bit) loosless encoding of Unicode characters. UTF-8 encodes each Unicode character as a variable number of 1 to 4 octets, where the number of octets depends on the integer value assigned to the Unicode character. It is an efficient encoding of Unicode documents that use mostly US-ASCII characters because it represents each character in the range U+0000 through U+007F as a single octet. UTF-8 is the default encoding for XML.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-8″ ?>’

>> UTF-16: encoding is the 16-bit encoding of Unicode. It use 2 bytes per character (and sometimes combines two pairs), it makes implementation easier, but looks a bit overkill for Western languages encoding.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-16″ ?>’

Let’s check both the encodings and issues if they are not used correctly:

-- ===================================================
-- XML containing NON Unicode English characters only:
-- ===================================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Happy Birthday</subject>
	<body>Wish you a very very Happy Birth Day!!!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

 Correct expected output:-

TO	FROM	SUBJECT		BODY
Gaurav	Garvit	Happy Birthday	Wish you a very very Happy Birth Day!!!

Let’s see how this works with international languages. Let’s change the language to French with same UTF-8 encoding:

-- ============================================
-- When XML contains Unicode French characters:
-- ============================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

We get an Error Message:
Msg 9420, Level 16, State 1, Line 2
XML parsing: line 6, character 31, illegal xml character

As a NON-English language (French) is used to so we have to use the UTF-16 encoding:

-- Let's change the encoding to UTF-16, i.e. for UNICODE characters:
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

Still getting the Error Message:
Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Now, as we are dealing with UNICODE data so the XML string should also be of UNICODE type, so I’ll prefix ‘N’ before the string starts in SET statement:

-- Add N for to make the xml string Non-Unicode:
DECLARE @xml as XML
SET @xml =
N'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

Correct expected output:-
TO	FROM	SUBJECT			BODY
Gaurav	Garvit	Joyeux anniversaire	Je vous souhaite une très très heureux anniversaire!

As per MS BOL: “SQL Server stores XML data using the UTF-16 encoding scheme. Because UTF-16 data is variable-width, it is processed according to a byte-oriented protocol. This means that UTF-16 data can be treated in a way that is independent of the byte ordering on different computers (little endian versus big endian). Therefore, UTF-16 is well-suited for traversing different computers that use different encodings and byte-ordering systems. Because XML data is typically shared widely across networks, it makes sense to maintain the default UTF-16 storage of XML data in your database, and when you export XML data to clients.”

More info on: http://msdn.microsoft.com/en-US/library/ms145866%28v=SQL.90%29.aspx

Categories: XML Tags: , , , , , ,

Adding Namespaces in XML | Querying XML with Namespaces (SQL Server)

November 2, 2011 1 comment

Namespaces like in every language provides a way to avoid conflict with element names. Here we will see how can we add Namespace to an XML. In previous posts we learned how to query XMLs, but without Namespaces. Here we will see how can we query XML containing Namespaces.

>> Creating an XML with Namespace prefix:

USE [AdventureWorks]
GO

;WITH XMLNAMESPACES ('uri' as ns)
select TOP 2 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] 
from AdventureWorks.Person.Contact 
FOR XML PATH('Contact'), ROOT('ns:Person.Contact'), ELEMENTS XSINIL

Output:- XML with the Namespace prefix

<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>

>> Querying the XML with Namespaces:

declare @xml xml

set @xml = '
<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>'

-- Declaring namespaces in XQuery
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 @xml.nodes('declare namespace ns="uri"; //ns:Person.Contact/Contact') T(C)

-- Using WITH XMLNAMESPACES
;WITH XMLNAMESPACES ('uri' as ns)
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 @xml.nodes('//ns:Person.Contact/Contact') as T(C)
Categories: XML Tags: , ,

Using DML with XML – UPDATE & DELETE (SQL Server)

October 31, 2011 Leave a comment

In my previous post we saw how we can INSERT records in a XML string. On this post we”ll see the UPDATE & DELETE operations by using same xml.modify() method.

DECLARE @xml XML

-- Taking the sample XML string we created in my previous post:
SET @xml=N'&amp;lt;Root&amp;gt;
  &amp;lt;stud id=&amp;quot;0&amp;quot;&amp;gt;
    &amp;lt;fname&amp;gt;Kanchan&amp;lt;/fname&amp;gt;
    &amp;lt;lname&amp;gt;Pandey&amp;lt;/lname&amp;gt;
    &amp;lt;class&amp;gt;10&amp;lt;/class&amp;gt;
    &amp;lt;marks&amp;gt;90.5&amp;lt;/marks&amp;gt;
  &amp;lt;/stud&amp;gt;
  &amp;lt;stud id=&amp;quot;1&amp;quot;&amp;gt;
    &amp;lt;fname&amp;gt;Manoj&amp;lt;/fname&amp;gt;
    &amp;lt;lname&amp;gt;Pandey&amp;lt;/lname&amp;gt;
    &amp;lt;class&amp;gt;10&amp;lt;/class&amp;gt;
    &amp;lt;marks&amp;gt;80.5&amp;lt;/marks&amp;gt;
  &amp;lt;/stud&amp;gt;
  &amp;lt;stud id=&amp;quot;2&amp;quot;&amp;gt;
    &amp;lt;fname&amp;gt;Saurabh&amp;lt;/fname&amp;gt;
    &amp;lt;lname&amp;gt;Sharma&amp;lt;/lname&amp;gt;
    &amp;lt;class&amp;gt;11&amp;lt;/class&amp;gt;
    &amp;lt;marks&amp;gt;82.7&amp;lt;/marks&amp;gt;
  &amp;lt;/stud&amp;gt;
  &amp;lt;stud id=&amp;quot;3&amp;quot;&amp;gt;
    &amp;lt;fname&amp;gt;Rajesh&amp;lt;/fname&amp;gt;
    &amp;lt;lname&amp;gt;Shah&amp;lt;/lname&amp;gt;
    &amp;lt;class&amp;gt;11&amp;lt;/class&amp;gt;
    &amp;lt;marks&amp;gt;70.3&amp;lt;/marks&amp;gt;
  &amp;lt;/stud&amp;gt;
&amp;lt;/Root&amp;gt;'

> UPDATE marks of Manoj to 78.2 (currently 80.5):

SET @xml.modify('
	replace value of (/Root/stud[@id=&amp;quot;1&amp;quot;]/marks/text())[1]
	with &amp;quot;78.2&amp;quot;')

SELECT @xml

> UPDATE marks of Rajesh to 78.7 stored in a variable:

DECLARE @marks FLOAT
SET @marks = 78.7

SET @xml.modify('
	replace value of (/Root/stud[@id=&amp;quot;3&amp;quot;]/marks/text())[1]
	with sql:variable(&amp;quot;@marks&amp;quot;)')

SELECT @xml

> DELETE Saurabh record where id = 2:

SET @xml.modify('
	delete (/Root/stud[@id=&amp;quot;2&amp;quot;])')

SELECT @xml

Final XML:

Categories: XML Tags: , , ,