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.
