Best way of flattening variable elements

Question

Hi,

I am reporting on some Audit data in SSRS, and the detail of the audit records varies depending on the aciton and the data being stored, e.g.:

<AuditData>
  <EntityBand>
    <Signonname>bob</Signonname>
    <Disallowlogon>false</Disallowlogon>
    <Requireusertochangepassword>false</Requireusertochangepassword>
    <GroupMembership />
    <Password>********</Password>
  </EntityBand>
</AuditData>

<AuditData>
  <EntityBand>
    <GroupName>admins</GroupName>
  </EntityBand>
</AuditData>

I can get the data that I'm interested in (element name and value) out with the following....

SELECT ea.id, 
       c.query('data(*)[1]')             AS 'Field1', 
       c.query('fn:local-name((*)[1])')  AS 'Field1Name', 
       c.query('data(*)[2]')             AS 'Field2', 
       c.query('fn:local-name((*)[2])')  AS 'Field2Name', 
       c.query('data(*)[3]')             AS 'Field3', 
       c.query('fn:local-name((*)[3])')  AS 'Field3Name'
-- etc...
FROM   extended_audit ea 
       CROSS apply ea.auditdata.nodes('/AuditData/EntityBand') x(c) 

But it doesn't seem very elegant - I'll have to find the maximum potential number of sub elements that could be stored repeat the select accordingly. Is there a better way to do it?

Many thanks

Answer 1

Use the descendant axis to get your child elements of EntityBand, when there are nested elements. E.g.

DECLARE @Sample TABLE ( ID INT , Data XML );

INSERT INTO @Sample VALUES
( 1, N'
<AuditData>
  <EntityBand>
    <Signonname>bob</Signonname>
    <Disallowlogon>false</Disallowlogon>
    <Requireusertochangepassword>false</Requireusertochangepassword>
    <GroupMembership />
    <Password>********</Password>
  </EntityBand>
</AuditData> 
' ),
( 2, N'
<AuditData>
  <EntityBand>
    <GroupName>admins</GroupName>
  </EntityBand>
</AuditData>
');

SELECT	S.ID,
		n.value('fn:local-name((.)[1])', 'NVARCHAR(255)')  AS Element,
		n.value('data(.)[1]', 'NVARCHAR(255)') AS Value       
FROM	@Sample S
		CROSS APPLY S.Data.nodes('/AuditData/EntityBand/descendant::*') R ( n );

Otherwise use the simple XPath /AuditData/EntityBand/*.

Answer 2

Perfect, thanks.

Answer 3

Sorry to re-open this, but whereas before with my query I was getting a single row per sub band with the revised query I now have based on your answer, the descendants are all in one long list and therefore I will struggle to group them together in my report, e.g.:

Previous result:

• Ref1, Value1a, Value1b, etc.
• Ref2, Value2a, Value2b, etc.

New result:

• Ref1
• Value1a
• Value1b
• Ref2
• Value2a
• Value2b
• etc.

Is it possible to add a grouping field for the children of each node to the result set, e.g. to get something like this:

• Ref1                  Ref1
• Ref1                  Value1a
• Ref1                  Value1b
• Ref2                  Ref2
• Ref2                  Value2a
• Ref2                  Value2b
• etc.

Thanks again

Answer 4

Either you have fixed data, then parse the XML using all kown elements. This is also faster as you can use the element names. Otherwise use a dynamic pivot, e.g.

DECLARE @Columns NVARCHAR(MAX);
DECLARE @Sample TABLE ( ID INT , Data XML );
DECLARE @Sql NVARCHAR(MAX);

INSERT INTO @Sample VALUES
( 1, N'
<AuditData>
  <EntityBand>
    <Signonname>bob</Signonname>
    <Disallowlogon>false</Disallowlogon>
    <Requireusertochangepassword>false</Requireusertochangepassword>
    <GroupMembership />
    <Password>********</Password>
  </EntityBand>
</AuditData> 
' ),
( 2, N'
<AuditData>
  <EntityBand>
    <GroupName>admins</GroupName>
  </EntityBand>
</AuditData>
');

SELECT	S.ID,
		n.value('fn:local-name((.)[1])', 'NVARCHAR(255)')  AS Element,
		n.value('data(.)[1]', 'NVARCHAR(MAX)') AS Value       
INTO	#EAV
FROM	@Sample S
		CROSS APPLY S.Data.nodes('/AuditData/EntityBand/descendant::*') R ( n );

SELECT	*
FROM	#EAV;

SELECT	@Columns = COALESCE(@Columns + ', ', '') + QUOTENAME(Element)
FROM	#EAV;

SET @Sql = N'
SELECT	*
FROM	#EAV
		PIVOT ( MIN(Value) FOR Element IN (' + @Columns + ') ) P;	
';

EXECUTE ( @Sql );

DROP TABLE #EAV;

Answer 5

As there is no sense in working with unkown columns, specify the elements. E.g.

SELECT	S.ID,		
		n.value('Signonname[1]', 'NVARCHAR(MAX)') AS Signonname,      
		n.value('Disallowlogon[1]', 'NVARCHAR(MAX)') AS Disallowlogon,      
		n.value('Requireusertochangepassword[1]', 'NVARCHAR(MAX)') AS Requireusertochangepassword,      
		n.value('GroupMembership[1]', 'NVARCHAR(MAX)') AS GroupMembership,      
		n.value('Password[1]', 'NVARCHAR(MAX)') AS Password,      
		n.value('GroupName[1]', 'NVARCHAR(MAX)') AS GroupName       
FROM	@Sample S
		CROSS APPLY S.Data.nodes('/AuditData/EntityBand') R ( n );

Answer 6

Thanks for the quick response. Unfortunately, in most cases my columns will be unknown as the data stored varies from event to event.

However, currently the only scenarios with child sub records do have known elements so I'll stick with those known elements for now.

Thanks again