Advertisements

Archive

Archive for the ‘XML’ Category

Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML

September 9, 2012 4 comments

In my previous post [Part 1] we saw how to pass multiple values to a parameter as a CSV string in an SP.
 

Here in the second part of this series we will use XML string that will contain the set of values and pass as an XML param variable to the SP. Then inside the SP we will parse this XML and use those values in our SQL Queries, just like we did in previous post with CSV string:

USE [AdventureWorks2012]
GO

-- Create an SP with XML type parameter:
CREATE PROCEDURE uspGetPersonDetailsXML (
	@persons XML
)
AS
BEGIN
	--DECLARE @persons XML
	--SET @persons = '<root><Name>Charles</Name><Name>Jade</Name><Name>Jim</Name><Name>Luke</Name><Name>Ken</Name></root>'

	SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
	INTO #tblPersons
	FROM @persons.nodes('/root/Name') as T(C)
	
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM #tblPersons tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

	DROP TABLE #tblPersons
END
GO

-- Create XML string:
DECLARE @xml XML
SET @xml = '<root>
				<Name>Charles</Name>
				<Name>Jade</Name>
				<Name>Jim</Name>
				<Name>Luke</Name>
				<Name>Ken</Name>
			</root>'

-- Use the XML string as parameter which calling the SP:
EXEC uspGetPersonDetailsXML @xml
GO
-- Check the output, objective achieved 🙂

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsXML
GO

 

This approach looks much cleaner and more stable to me than the previous one (CSV). XML is a de-facto standard to store and transmit data in a more structured way. Thus I prefer XML string over the CSV string on these type of cases.
 

The CSV approach is no different than this one, it internally converts the CSV string to an XML then parse it. Thus, like CSV if the XML string becomes lengthy it will also take time to parse the whole XML and then use the values in SQL queries in the SP.
 

In next [blog post] we will see how we can perform the same operation by using temporary (temp, #) tables.

Advertisements

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.

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

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)

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: , , ,