SQL Yoga: Parse XML with namespace with SQL SERVER

Recently, we were working on XML having namespace and we need to read that XML to fetch the information from the XML. Here, is the Sample XML which we are trying to parse: DECLARE @x XML SELECT @x = '     <X                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xmlns:xsd="http://www.w3.org/2001/XMLSchema"                 xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">         <info>Sample XML TEST</info>            </X>              ' If we need to fetch the “info” element with T-SQL we can write query as follows: ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')     SELECT  x.v.value('info[1]','VARCHAR(50)')     FROM    @x.nodes('/X') x (v) Here, we have to use “WITH XMLNAMESPACES”, so we can specify the namespace of the XML that we are reading, else it wont give us any result.

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server. To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in previous post. Read XML Elements with T-SQL: DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' DECLARE @xml XML SELECT @xml = (         SELECT    *         FROM    @SQLYoga         FOR XML PATH('Record'), ROOT('Records')     ) SELECT @xml Now, please find query to read the query to read XML generated above: SELECT         x.v.value('ID[1]', 'INT') AS ID,         x.v.value('Data[1]', 'VARCHAR(50)') As Data,         x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate FROM    @xml.nodes('/Records/Record') x(v) This query generates the output as follows: That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database. There are two ways to have an XML from query: 1. FOR XML RAW 2. FOR XML PATH Please find following sample queries where both of the way are being used: Lets create sample data DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' Generate XML with XML ROW SELECT * FROM @SQLYoga FOR XML RAW, ROOT('Records') XML RAW, returns the XML by each record with row element and all columns as attributes in the XML. Generate XML with XML PATH SELECT    * FROM    @SQLYoga FOR XML PATH('Record'), ROOT('Records') XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record. That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

SQL SERVER: Check if Node exists in XML or not

Today, I have one requirement to check dynamically if a node exists in my xml or NOT. I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic. I figure it out by using EXISTS. This is my XML, that I got as parameter. DECLARE @ExportData XML SELECT @ExportData = '<Data Number="A123"> <BulkData> <EachData Parts="Test1" /> <EachData Parts="Test2" /> <EachData Parts="Test3" /> </BulkData> </Data>' Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result. So, I used this SELECT @ExportData.exist('(//BulkData)') This will return "1" if node is exists else return "0". That's it. I can write based on the return result by this statement. Let me know if it helps you. Reference : Tejas Shah(http://www.SQLYoga.com)

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

 

Example:

I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.

 

Proposed Solution:

Convert VARCHAR variable to XML as follows:

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )
            

 

So, it is very easy to read values from Comma separated value.

 

Let me know if it helps you.

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.

 

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

 

Example:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Expected Output:

Proposed Solution:

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

My Output will be:

 

Please make comments, if this helps you in any way