November 18, 2014 at 5:42 pm
How would I parse a recursive xml as given below. As you can see I have an indefinite self replicating xml node below and I would like to be able to parse those and store it in the relational sql table. Here the xml goes to three levels deep but it could be less or many layers more. The immediate upper element is the parent. Any help would be really appreciated.
DECLARE @xml x='
<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>
</SecureEvent>'
Here in this example, I need to parse the values into two tables SecureEvent and Secure and store their respective attribute/element values and the parent child relationship.Thanks.
November 18, 2014 at 7:36 pm
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
November 20, 2014 at 2:25 pm
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
Thanks Eririkur. This certainly gives me a good direction. What if there were multiple secures within a SecurEvent?
November 20, 2014 at 9:57 pm
peacesells (11/20/2014)
Thanks Eririkur. This certainly gives me a good direction. What if there were multiple secures within a SecurEvent?
Multiple "Secure/SecureType" elements will multiply the outer/parent "SecureEvent" element, as this is an one-to-many relationship. The two most obvious ways of handling this are either to enumerate the outer/parent instances (row_number) or shred the inner element separately in another query.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply