XML SQL | SQL with Manoj

Parse or Query XML column with XMLNAMESPACES (xmlns namespace) – MSDN TSQL forum

January 15, 2016 Manoj Pandey Leave a comment

–> Question:

We have SQL audit information.
We would like to select XML column in some user friendly way.

CREATE TABLE [dbo].[audit](
	[server_instance_name] [NVARCHAR](128) NULL,
	[statement] [NVARCHAR](4000) NULL,
	[additional_information] XML NULL
)


INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv1','sp_addlinkedsrvlogin','')

INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv2','','<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>')

SELECT * FROM [dbo].[audit]

Output of the XML column:

Required Output:

–> Answer:

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data' as ns)
SELECT 
	 [server_instance_name]
	,[statement]
	,[additional_information]
	,t.c.value ('ns:session[1]', 'varchar(50)') AS session
	,t.c.value ('ns:action[1]', 'varchar(50)') AS action
	,t.c.value ('ns:startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('ns:object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//ns:action_info') as t(c)
GO

-- OR -- 

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
SELECT 
	 [server_instance_name]
	,[statement]
	,t.c.value ('session[1]', 'varchar(50)') AS session
	,t.c.value ('action[1]', 'varchar(50)') AS action
	,t.c.value ('startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//action_info') as t(c)
GO

Drop table finally

DROP TABLE [audit]
GO

Ref link.

Reading XML data in SQL Server – MSDN TSQL forum

May 26, 2015 Manoj Pandey Leave a comment

–> Question:

I have a table with a column with data type XML.

I don’t know the contents of the XML structure etc and I need to extract it

Please suggest.

–> My Answer:

Check these blog posts on how to query and work with XML data in SQL:

– Convert a table to XML and back to tabular format

– Query XML data in tabulat format

– Query Nested XML

– Using APPLY operator

– Read XML from a file

– XML with Namespaces

– Export XML to a file

– All XML posts

Ref Link.

Categories: SQL Server Questions, XML Tags: MSDN TSQL forum, XML SQL

Convert XML to Columns – MSDN TSQL forum

July 13, 2013 Manoj Pandey Leave a comment

–> Question:

Below is the XML that i need to covert into columns please help, XML is coming from column name called “DESC” table name is “rawXML”

Columns: USER id, US_USERID, US_PASSWORD, US_SHORT, FIRST, LAST, US_LAST_PASSWORD_UPDATE

<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>

–>My Answer:

Check the query below:

declare @xml xml
set @xml = '<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>'

select 
	t.c.value('../@id[1]', 'varchar(10)') as [USER],
	t.c.value('../US_USERID[1]', 'varchar(10)') as [US_USERID],
	t.c.value('../US_PASSWORD[1]', 'varchar(10)') as [US_PASSWORD],
	t.c.value('../US_SHORT[1]', 'varchar(10)') as [US_SHORT],
	t.c.value('./FIRST[1]', 'varchar(10)') as [FIRST],
	t.c.value('./LAST[1]', 'varchar(10)') as [LAST],
	t.c.value('../US_LAST_PASSWORD_UPDATE[1]', 'varchar(10)') as [US_LAST_PASSWORD_UPDATE]
from @xml.nodes('//USER/US_XPN') as t(c)

Ref Link.

Categories: SQL Server Questions, XML Tags: MSDN TSQL forum, XML SQL

XML to Table – MSDN TSQL forum

February 28, 2013 Manoj Pandey Leave a comment

–>Question:

I’m using sql server 2000 and here is my target table:

CREATE TABLE #SampleTable
(
    Number varchar(100),
    StartNum int
)

I want to parse the XML and insert into the above table:

<activateNumber>
         <!--You may enter ANY elements at this point-->
         <number>1234</number>
         <StaartNumbers>
            <StartNum>234</StartNum>
         </StaartNumbers>
</activateNumber>

–> My Answer:

Check the code below:

CREATE TABLE #SampleTable
(
    Number varchar(100),
    StartNum int
)

DECLARE @xml XML = '<activateNumber>
         <!--You may enter ANY elements at this point-->
         <number>1234</number>
         <StaartNumbers>
            <StartNum>234</StartNum>
         </StaartNumbers>
</activateNumber>'

DECLARE @iDoc int
EXEC sp_xml_preparedocument @iDoc output, @xml

INSERT INTO #SampleTable
SELECT 
	number,
	StartNum
FROM OPENXML(@iDoc,'/activateNumber/StaartNumbers',2)
WITH (number INT '../number[1]', 
		StartNum INT 'StartNum[1]')

EXEC sp_xml_removedocument @iDoc

Ref Link.

Categories: SQL Server Questions, XML Tags: MSDN TSQL forum, XML SQL

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

September 9, 2012 Manoj Pandey 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.