Eirikur Eiriksson (11/18/2014)

---

Quick solution which uses a recursive CTE, should be enough to get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML =

'<SecureEvent EDate="08/04/2011" ID="47854303" InternalSecureEventID="154472406">

<SecureEventDate>08/04/2011</SecureEventDate>

<TimestampCreate>08/04/2011 00:00:00:000</TimestampCreate>

<WatchActionKey Word="N">Does not affect aging clock</WatchActionKey>

<Palung Word="UN">Unassigned, Palung</Palung>

<SecureBPalung Word="CONV">Conversion - B Palung</SecureBPalung>

<Secure ActionID="2948979" ActionReviewID="7013201" InternalActionID="6822517" InternalReviewID="24653683">

<SecureType Word="PLGY">OK</SecureType>

</Secure>

<SecureEvent EDate="07/11/2011" ID="47854302" InternalSecureEventID="154472405">

<SecureEventDate>07/11/2011</SecureEventDate>

<TimestampCreate>07/11/2011 00:00:00:000</TimestampCreate>

<WatchActionKey Word="N">Does not affect </WatchActionKey>

<Palung Word="UN">Unassigned, Palung</Palung>

<Secure ActionID="2948979" ActionReviewID="7013200" InternalActionID="6822517" InternalReviewID="24653682">

<SecureType Word="PLNG">Not OK</SecureType>

</Secure>

<SecureEvent EDate="09/08/2011" ID="47854302" InternalSecureEventID="154472405">

<SecureEventDate>07/11/2011</SecureEventDate>

<TimestampCreate>07/11/2011 00:00:00:000</TimestampCreate>

<WatchActionKey Word="N">Does not affect </WatchActionKey>

<Palung Word="UN">Unassigned, Palung</Palung>

<Secure ActionID="454455" ActionReviewID="25356" InternalActionID="6822517" InternalReviewID="34234343">

<SecureType Word="PLNG">Not OK</SecureType>

</Secure>

</SecureEvent>

<SecureEvent EDate="09/08/2011" ID="47854303" InternalSecureEventID="154472406">

<SecureEventDate>07/11/2011</SecureEventDate>

<TimestampCreate>07/11/2011 00:00:00:000</TimestampCreate>

<WatchActionKey Word="N">Does not affect </WatchActionKey>

<Palung Word="UN">Unassigned, Palung</Palung>

<Secure ActionID="454455" ActionReviewID="25356" InternalActionID="6822517" InternalReviewID="34234343">

<SecureType Word="PLNG">Not OK</SecureType>

</Secure>

</SecureEvent>

</SecureEvent>

</SecureEvent>';

;WITH XML_SOURCE AS

(

SELECT

CONVERT(INT,1,0) AS XS_ID

,@TXML AS XS_XML

)

,BASE_XML AS

(

SELECT

1 AS LEVEL_NO

,0 AS PARTENT_LEVEL_NO

,XPLAN.DATA.value('local-name(.)','VARCHAR(1024)') AS NODE_NAME

,XPLAN.DATA.query('.') AS NODE_XML

,XPLAN.DATA.query('SecureEvent') AS SUB_NODE_XML

FROM XML_SOURCE RP OUTER APPLY

RP.XS_XML.nodes('*') AS XPLAN(DATA)

UNION ALL

SELECT

BX.LEVEL_NO + 1 AS LEVEL_NO

,BX.LEVEL_NO AS PARTENT_LEVEL_NO

,XPLAN.DATA.value('local-name(.)','VARCHAR(1024)') AS NODE_NAME

,XPLAN.DATA.query('.') AS NODE_XML

,XPLAN.DATA.query('SecureEvent') AS SUB_NODE_XML

FROM BASE_XML BX

OUTER APPLY BX.SUB_NODE_XML.nodes('*') AS XPLAN(DATA)

WHERE XPLAN.DATA.query('.') IS NOT NULL

)

,BASE_ELEMENTS AS

(

SELECT

BX.LEVEL_NO

,BX.PARTENT_LEVEL_NO

,ROW_NUMBER() OVER

(

PARTITION BY BX.LEVEL_NO

ORDER BY (SELECT NULL)

) AS LEVEL_RID

,NODE_NAME

,NODE_XML

FROM BASE_XML BX

)

SELECT

BE.LEVEL_NO

,BE.PARTENT_LEVEL_NO

,BE.LEVEL_RID

,BE.NODE_NAME

,BASE.DATA.value('@EDate' ,'DATE') AS EDate

,BASE.DATA.value('@ID' ,'BIGINT') AS ID

,BASE.DATA.value('@InternalSecureEventID' ,'BIGINT') AS InternalSecureEventID

,BASE.DATA.value('SecureEventDate[1]' ,'DATE') AS SecureEventDate

,BASE.DATA.value('TimestampCreate[1]' ,'DATETIME') AS TimestampCreate

,BASE.DATA.value('WatchActionKey[1]' ,'NVARCHAR(100)') AS WatchActionKey

,BASE.DATA.value('WatchActionKey[1]/@Word' ,'NVARCHAR(20)') AS WatchActionKey_Word

,BASE.DATA.value('Palung[1]' ,'NVARCHAR(100)') AS Palung

,BASE.DATA.value('Palung[1]/@Word' ,'NVARCHAR(20)') AS Palung_Word

,BASE.DATA.value('Secure[1]' ,'NVARCHAR(100)') AS Secure

,BASE.DATA.value('Secure[1]/@ActionID' ,'BIGINT') AS Secure_ActionID

,BASE.DATA.value('Secure[1]/@ActionReviewID' ,'BIGINT') AS Secure_ActionReviewID

,BASE.DATA.value('Secure[1]/@InternalActionID','BIGINT') AS Secure_InternalActionID

,BASE.DATA.value('Secure[1]/@InternalReviewID','BIGINT') AS Secure_InternalReviewID

,SECURE.DATA.value('@Word' ,'NVARCHAR(20)') AS SecureType_Word

,SECURE.DATA.value('.[1]' ,'NVARCHAR(100)') AS SecureType

FROM BASE_ELEMENTS BE

OUTER APPLY BE.NODE_XML.nodes('*') AS BASE(DATA)

OUTER APPLY BASE.DATA.nodes('Secure/SecureType') AS SECURE(DATA);

Results

LEVEL_NO PARTENT_LEVEL_NO LEVEL_RID NODE_NAME EDate ID InternalSecureEventID SecureEventDate TimestampCreate WatchActionKey WatchActionKey_Word Palung Palung_Word Secure Secure_ActionID Secure_ActionReviewID Secure_InternalActionID Secure_InternalReviewID SecureType_Word SecureType

--------- ---------------- ---------- ------------- ---------- --------- --------------------- --------------- ----------------------- ---------------------------- -------------------- -------------------- ------------ --------- ---------------- --------------------- ----------------------- ----------------------- ---------------- ----------

1 0 1 SecureEvent 2011-08-04 47854303 154472406 2011-08-04 2011-08-04 00:00:00.000 Does not affect aging clock N Unassigned, Palung UN OK 2948979 7013201 6822517 24653683 PLGY OK

2 1 1 SecureEvent 2011-07-11 47854302 154472405 2011-07-11 2011-07-11 00:00:00.000 Does not affect N Unassigned, Palung UN Not OK 2948979 7013200 6822517 24653682 PLNG Not OK

3 2 1 SecureEvent 2011-09-08 47854302 154472405 2011-07-11 2011-07-11 00:00:00.000 Does not affect N Unassigned, Palung UN Not OK 454455 25356 6822517 34234343 PLNG Not OK

3 2 2 SecureEvent 2011-09-08 47854303 154472406 2011-07-11 2011-07-11 00:00:00.000 Does not affect N Unassigned, Palung UN Not OK 454455 25356 6822517 34234343 PLNG Not OK