none
parsing XML

    Question

  • Hi,

    We have SQL audit information:

    CREATE TABLE [dbo].[audit]([server_instance_name] [NVARCHAR](128) NULL,[statement] [NVARCHAR](4000) NULL,[additional_information] [NVARCHAR](4000) 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>')

    We would like to select [additional_information] in some user friendly way:

    SELECT * FROM dbo.audit

    [server_instance_name] | [statement] | [additional_information]
    srv1 |  sp_addlinkedsrvlogin |
    srv2 |  | Audit$Aevent enabledma

    Is it possible to change XML by using like CASE statement?

    Thanks



    • Edited by jori5 Wednesday, January 13, 2016 4:59 PM
    Wednesday, January 13, 2016 4:58 PM

Answers

  • Hi Jori5,

    According to the expected output, are your looking for some sample as below?

    ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data'),
    Cte AS(
    SELECT server_instance_name,statement,CAST(additional_information AS XML) additional_information FROM AUDIT
    )
    ,
    Cte2 AS
    (
    SELECT server_instance_name,statement,x.n.value('.','varchar(200)') additional_information FROM CTE
    OUTER APPLY
    additional_information.nodes('/action_info/*') x(n)
    )
    SELECT server_instance_name,statement,STUFF((SELECT ','+additional_information FROM CTE2 WHERE C.server_instance_name=server_instance_name AND c.statement=statement FOR XML PATH('')),1,1,'') additional_information FROM CTE2 c
    GROUP BY server_instance_name,statement

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    Thursday, January 14, 2016 8:44 AM
    Moderator

All replies

  • Question isn't clear. Are you trying to update the xml? Or change the resultset?
    Wednesday, January 13, 2016 5:07 PM
  • If you want to search then you can use Full-Text Search with XML Columns:

    https://msdn.microsoft.com/en-us/library/bb522491.aspx?f=255&MSPPError=-2147217396

    regarding the second question it is not clear to me what you want to do. Please clarify


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, January 13, 2016 6:17 PM
    Moderator
  • We would like to select (SELECT [additional_information] FROM dbo.audit WHERE [server_instance_name] = 'srv2') in user friendly view, something like "Audit$A; event; enabled; ma"

    Thanks

    • Edited by jori5 Thursday, January 14, 2016 7:04 AM
    Thursday, January 14, 2016 7:03 AM
  • Check this Query:

    ;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)') 
    		+ t.c.value ('ns:action[1]', 'varchar(50)') 
    		+ t.c.value ('ns:startup_type[1]', 'varchar(50)')
    		+ t.c.value ('ns:object[1]', 'varchar(50)') as additional_information_from_xml
    from [audit] as a
    outer apply a.additional_information.nodes('//ns:action_info') as t(c)

    Check more on Querying XMLs here: http://sqlwithmanoj.com/category/sql-server-internals/xml/


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page

    Thursday, January 14, 2016 8:15 AM
  • Hi Jori5,

    According to the expected output, are your looking for some sample as below?

    ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data'),
    Cte AS(
    SELECT server_instance_name,statement,CAST(additional_information AS XML) additional_information FROM AUDIT
    )
    ,
    Cte2 AS
    (
    SELECT server_instance_name,statement,x.n.value('.','varchar(200)') additional_information FROM CTE
    OUTER APPLY
    additional_information.nodes('/action_info/*') x(n)
    )
    SELECT server_instance_name,statement,STUFF((SELECT ','+additional_information FROM CTE2 WHERE C.server_instance_name=server_instance_name AND c.statement=statement FOR XML PATH('')),1,1,'') additional_information FROM CTE2 c
    GROUP BY server_instance_name,statement

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    Thursday, January 14, 2016 8:44 AM
    Moderator
  • Thanks Eric
    Thursday, January 14, 2016 9:17 AM