Archive
Parse or Query XML column with XMLNAMESPACES (xmlns namespace) – MSDN TSQL forum
–> 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
–> 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
Ref Link.
Convert XML to Columns – MSDN TSQL forum
–> 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.
XML to Table – MSDN TSQL forum
–>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.
Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML
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.