Xquery

Question

Post reply

Xquery

peacesells

SSCrazy

Points: 2882
More actions
October 3, 2014 at 6:03 pm

#100194

<EventOne ID="9999" >
<EventDate>09/22/1984</EventDate>
<PartyID2 InternalPartyID="2">90</PartyID2>
<PartyID2 InternalPartyID="4">100</PartyID2>
<PartyID InternalPartyID="6">110</PartyID>
<PartyID InternalPartyID="8">120</PartyID>
</EventOne>
Using the Xquery how would i parse the xml above into two one-to-many relational tables below.
CREATE TABLE EventOne (EventOneID INT) --Parent Table
CREATE TABLE PartyID (EventOneID INT,PartyID BIGINT, InternalPartyID BIGINT) --Child Table
Id like the first table to have the EventOneID and all the PartyID and internalpartyid should go to the child table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 1

This might help get you started. I am no expert at XQuery and there are probably better ways to do this.

declare @xml xml

set @xml =

'<EventOne ID="55" >

<EventDate>09/22/1984</EventDate>

<PartyID2 InternalPartyID="2">90</PartyID2>

<PartyID2 InternalPartyID="4">100</PartyID2>

<PartyID InternalPartyID="6">110</PartyID>

<PartyID InternalPartyID="8">120</PartyID>

</EventOne>

<EventOne ID="33" >

<EventDate>09/22/1984</EventDate>

<PartyID2 InternalPartyID="5">90</PartyID2>

<PartyID2 InternalPartyID="6">100</PartyID2>

<PartyID InternalPartyID="9">110</PartyID>

<PartyID InternalPartyID="8">120</PartyID>

</EventOne>'

IF OBJECT_ID('tempdb..#EventList', 'u') IS NOT NULL

DROP TABLE #EventList

SELECT c.value('./@ID', 'int') as EventOneID

,c.value('./EventDate[1]', 'DateTime') as EventDate

,c.value('./PartyID2[1]', 'int') as PartyID

,c.value('./PartyID2[1]/@InternalPartyID', 'int') as InternalPartyID

INTO #EventList

FROM @xml.nodes('/EventOne') as t(c)

UNION

SELECT c.value('./@ID', 'int') as EventOneID

,c.value('./EventDate[1]', 'DateTime') as EventDate

,c.value('./PartyID2[2]', 'int') as PartyID

,c.value('./PartyID2[2]/@InternalPartyID', 'int') as InternalPartyID

FROM @xml.nodes('/EventOne') as t(c)

UNION

SELECT c.value('./@ID', 'int') as EventOneID

,c.value('./EventDate[1]', 'DateTime') as EventDate

,c.value('./PartyID[1]', 'int') as PartyID

,c.value('./PartyID[1]/@InternalPartyID', 'int') as InternalPartyID

FROM @xml.nodes('/EventOne') as t(c)

UNION

SELECT c.value('./@ID', 'int') as EventOneID

,c.value('./EventDate[1]', 'DateTime') as EventDate

,c.value('./PartyID[2]', 'int') as PartyID

,c.value('./PartyID[2]/@InternalPartyID', 'int') as InternalPartyID

FROM @xml.nodes('/EventOne') as t(c)

SELECT DISTINCT EventOneID

FROM #EventList

SELECT EventOneID

,PartyID

,InternalPartyID

FROM #EventList

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Here is a simpler and better performing version of the XQuery, where the XQuery does the leg-work instead of using a union in the TSQL part. The result set contains all the information from the XML and inserting it into whichever table structure desired should be straight forward.

😎

declare @TXML xml

set @TXML =

'<EventOne ID="55">

<EventDate>09/22/1984</EventDate>

<PartyID2 InternalPartyID="2">90</PartyID2>

<PartyID2 InternalPartyID="4">100</PartyID2>

<PartyID InternalPartyID="6">110</PartyID>

<PartyID InternalPartyID="8">120</PartyID>

</EventOne>

';

SELECT

RN.DATA.value('@ID','INT') AS EventOne_ID

,RN.DATA.value('EventDate[1]','varchar(50)') AS EventDate

,PARTY.DATA.value('@InternalPartyID','INT') AS InternalPartyID

,PARTY.DATA.value('.[1]','INT') AS InternalPartyValue

,PARTY.DATA.value('local-name(.)','varchar(50)') AS InternalPartyName

FROM @TXML.nodes('EventOne') AS RN(DATA)

OUTER APPLY RN.DATA.nodes('*') PARTY(DATA)

WHERE PARTY.DATA.exist('@InternalPartyID') = 1

Results

EventOne_ID EventDate InternalPartyID InternalPartyValue InternalPartyName

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

55 09/22/1984 2 90 PartyID2

55 09/22/1984 4 100 PartyID2

55 09/22/1984 6 110 PartyID

55 09/22/1984 8 120 PartyID

peacesells SSCrazy Points: 2882 More actions · Answer 3

<Comment>Comment One!!</Comment>

</EventZ>

</EventZ>

<EventType Word="PLIN">Three</EventType>

</EventZ>

</EventOne>

Thank You guys, this is really helpful. I just ran to another problem.

I have this recursive xml that i need to store in a relational sql server table. The situation is I do not know how many nested layers i would get in a file. For instance i have 3 nested node (EventOne) in this example. It could be 2 or 6 in the other files.

How would i go about this. Thank You.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Here is a quick solution, more for prototyping but it works

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @TXML XML = '<EventOne Date="09/22/2014" ID="12196996" InternalEventOneID="96436969" xmlns:reslib="urn:reslib">

<EventOneDate>09/22/2014</EventOneDate>

<TimestampCreate>09/22/2014 11:37:06:413</TimestampCreate>

<Comment>Comment One!!</Comment>

<EventZ EventID="834033" EventHistoryID="375381" InternalEventID="4843699" InternalHistoryID="17082526">

<EventType Word="PLAD">One</EventType>

</EventZ>

<EventOne Date="09/22/2014" ID="12196995" InternalEventOneID="96436968">

<EventOneDate>09/22/2014</EventOneDate>

<TimestampCreate>09/22/2014 11:35:02:193</TimestampCreate>

<TimestampChange>09/22/2014 11:37:06:410</TimestampChange>

<EventZ EventID="834033" EventHistoryID="375380" InternalEventID="4843699" InternalHistoryID="17082525">

<EventType Word="PLDN">Two</EventType>

</EventZ>

<EventOne Date="09/22/2014" ID="12196992" InternalEventOneID="96436963">

<EventOneDate>09/22/2014</EventOneDate>

<TimestampCreate>09/22/2014 11:31:35:863</TimestampCreate>

<TimestampChange>09/22/2014 11:35:02:190</TimestampChange>

<EventZ EventID="834033" EventHistoryID="375378" InternalEventID="4843699" InternalHistoryID="17082523">

<EventType Word="PLIN">Three</EventType>

</EventZ>

</EventOne>

</EventOne>';

;WITH EVENT_DATA AS

(

SELECT

1 AS LEVEL_NO

,RN.DATA.value('@ID','INT') AS EventOne_ID

,PARTY.DATA.value('.[1]','varchar(50)') AS NodeValue

,PARTY.DATA.value('local-name(.)','varchar(50)') AS NodeName

,EVENTONE.DATA.query('(.)') AS SUB_EVENT

FROM @TXML.nodes('EventOne') AS RN(DATA)

OUTER APPLY RN.DATA.nodes('*') AS PARTY(DATA)

OUTER APPLY RN.DATA.nodes('EventOne') AS EVENTONE(DATA)

WHERE PARTY.DATA.value('local-name(.)','varchar(50)') <> 'EventOne'

UNION ALL

SELECT

ED.LEVEL_NO + 1 AS LEVEL_NO

,RN.DATA.value('@ID','INT') AS EventOne_ID

,PARTY.DATA.value('.[1]','varchar(50)') AS NodeValue

,PARTY.DATA.value('local-name(.)','varchar(50)') AS NodeName

,EVENTONE.DATA.query('(.)') AS SUB_EVENT

FROM EVENT_DATA ED

OUTER APPLY ED.SUB_EVENT.nodes('EventOne') AS RN(DATA)

OUTER APPLY RN.DATA.nodes('*') AS PARTY(DATA)

OUTER APPLY RN.DATA.nodes('EventOne') AS EVENTONE(DATA)

WHERE PARTY.DATA.value('local-name(.)','varchar(50)') <> 'EventOne'

)

SELECT DISTINCT

LEVEL_NO

,EventOne_ID

,NodeName

,NodeValue

FROM EVENT_DATA;

Results

LEVEL_NO EventOne_ID NodeName NodeValue

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

1 12196996 Comment Comment One!!

1 12196996 EventOneDate 09/22/2014

1 12196996 EventZ One

1 12196996 TimestampCreate 09/22/2014 11:37:06:413

2 12196995 EventOneDate 09/22/2014

2 12196995 EventZ Two

2 12196995 TimestampChange 09/22/2014 11:37:06:410

2 12196995 TimestampCreate 09/22/2014 11:35:02:193

3 12196992 EventOneDate 09/22/2014

3 12196992 EventZ Three

3 12196992 TimestampChange 09/22/2014 11:35:02:190

3 12196992 TimestampCreate 09/22/2014 11:31:35:863

peacesells SSCrazy Points: 2882 More actions · Answer 5

Eirikur Eiriksson (10/6/2014)
Here is a quick solution, more for prototyping but it works
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @TXML XML = '<EventOne Date="09/22/2014" ID="12196996" InternalEventOneID="96436969" xmlns:reslib="urn:reslib">
<EventOneDate>09/22/2014</EventOneDate>
<TimestampCreate>09/22/2014 11:37:06:413</TimestampCreate>
<Comment>Comment One!!</Comment>
<EventZ EventID="834033" EventHistoryID="375381" InternalEventID="4843699" InternalHistoryID="17082526">
<EventType Word="PLAD">One</EventType>
</EventZ>
<EventOne Date="09/22/2014" ID="12196995" InternalEventOneID="96436968">
<EventOneDate>09/22/2014</EventOneDate>
<TimestampCreate>09/22/2014 11:35:02:193</TimestampCreate>
<TimestampChange>09/22/2014 11:37:06:410</TimestampChange>
<EventZ EventID="834033" EventHistoryID="375380" InternalEventID="4843699" InternalHistoryID="17082525">
<EventType Word="PLDN">Two</EventType>
</EventZ>
<EventOne Date="09/22/2014" ID="12196992" InternalEventOneID="96436963">
<EventOneDate>09/22/2014</EventOneDate>
<TimestampCreate>09/22/2014 11:31:35:863</TimestampCreate>
<TimestampChange>09/22/2014 11:35:02:190</TimestampChange>
<EventZ EventID="834033" EventHistoryID="375378" InternalEventID="4843699" InternalHistoryID="17082523">
<EventType Word="PLIN">Three</EventType>
</EventZ>
</EventOne>
</EventOne>
</EventOne>';
;WITH EVENT_DATA AS
(
SELECT
1 AS LEVEL_NO
,RN.DATA.value('@ID','INT') AS EventOne_ID
,PARTY.DATA.value('.[1]','varchar(50)') AS NodeValue
,PARTY.DATA.value('local-name(.)','varchar(50)') AS NodeName
,EVENTONE.DATA.query('(.)') AS SUB_EVENT
FROM @TXML.nodes('EventOne') AS RN(DATA)
OUTER APPLY RN.DATA.nodes('*') AS PARTY(DATA)
OUTER APPLY RN.DATA.nodes('EventOne') AS EVENTONE(DATA)
WHERE PARTY.DATA.value('local-name(.)','varchar(50)') <> 'EventOne'
UNION ALL
SELECT
ED.LEVEL_NO + 1 AS LEVEL_NO
,RN.DATA.value('@ID','INT') AS EventOne_ID
,PARTY.DATA.value('.[1]','varchar(50)') AS NodeValue
,PARTY.DATA.value('local-name(.)','varchar(50)') AS NodeName
,EVENTONE.DATA.query('(.)') AS SUB_EVENT
FROM EVENT_DATA ED
OUTER APPLY ED.SUB_EVENT.nodes('EventOne') AS RN(DATA)
OUTER APPLY RN.DATA.nodes('*') AS PARTY(DATA)
OUTER APPLY RN.DATA.nodes('EventOne') AS EVENTONE(DATA)
WHERE PARTY.DATA.value('local-name(.)','varchar(50)') <> 'EventOne'
)
SELECT DISTINCT
LEVEL_NO
,EventOne_ID
,NodeName
,NodeValue
FROM EVENT_DATA;
Results
LEVEL_NO EventOne_ID NodeName NodeValue
----------- ----------- ---------------- -------------------------
1 12196996 Comment Comment One!!
1 12196996 EventOneDate 09/22/2014
1 12196996 EventZ One
1 12196996 TimestampCreate 09/22/2014 11:37:06:413
2 12196995 EventOneDate 09/22/2014
2 12196995 EventZ Two
2 12196995 TimestampChange 09/22/2014 11:37:06:410
2 12196995 TimestampCreate 09/22/2014 11:35:02:193
3 12196992 EventOneDate 09/22/2014
3 12196992 EventZ Three
3 12196992 TimestampChange 09/22/2014 11:35:02:190
3 12196992 TimestampCreate 09/22/2014 11:31:35:863

Thanks Eirikur. This is really helpful. I am very new to the xquery world and getting to learn a lot.

<InactiveAttorney>false</InactiveAttorney>

</Person>

<InactiveAttorney>false</InactiveAttorney>

</Person>

In the above example, Id like to be select the current phone number if it indicates that its current, if it does not say current just select a phone number. How would i do with xquery?

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 6

This is even more straight forward to do using the row_number function and set the order to select Current first if it exists, here is a quick sample

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @TXML XML = N'

<Persons>

<Person ID="776">

<EmailAddress>abc@abc.com</EmailAddress>

<NotifyElectronically>0</NotifyElectronically>

<InactiveAttorney>false</InactiveAttorney>

<PhoneNumber>127</PhoneNumber>

<PhoneNumber Current="true">258</PhoneNumber>

<PhoneNumber>123</PhoneNumber>

<HouseNumber>369</HouseNumber>

</Person>

<Person ID="777">

<EmailAddress>abcd@abc.com</EmailAddress>

<NotifyElectronically>0</NotifyElectronically>

<InactiveAttorney>false</InactiveAttorney>

<PhoneNumber>123</PhoneNumber>

<PhoneNumber>393</PhoneNumber>

<HouseNumber>369</HouseNumber>

</Person>

</Persons>

';

;WITH BASE_DATA AS

(

SELECT

PERSON.DATA.value('@ID','INT') AS ID

,ROW_NUMBER() OVER

(

PARTITION BY PERSON.DATA.value('@ID','INT')

ORDER BY ISNULL(PHONE.DATA.value('@Current','VARCHAR(10)'),'0') DESC

) AS PHONE_RID

,PERSON.DATA.value('EmailAddress[1]','VARCHAR(100)') AS EmailAddress

,PERSON.DATA.value('NotifyElectronically[1]','TINYINT') AS NotifyElectronically

,PHONE.DATA.value('../PhoneNumber[1]','VARCHAR(25)') AS PhoneNumber

,PHONE.DATA.value('@Current','VARCHAR(10)') AS [Current]

FROM @TXML.nodes('Persons/Person') AS PERSON(DATA)

OUTER APPLY PERSON.DATA.nodes('PhoneNumber') AS PHONE(DATA)

)

SELECT

BD.ID

,BD.EmailAddress

,BD.NotifyElectronically

,BD.PhoneNumber

,ISNULL(BD.[Current],'false') AS [Current]

FROM BASE_DATA BD

WHERE BD.PHONE_RID = 1;

Results

ID EmailAddress NotifyElectronically PhoneNumber Current

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

776 abc@abc.com 0 127 true

777 abcD@abd.com 0 123 false