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.
Export XML column data to a file.xml
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.
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.