Xquery

  • <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.

  • 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

  • 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

  • <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>

    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.

  • 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

  • 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.

    <Person ID="777" >

    <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>abc@abc.com</EmailAddress>

    <NotifyElectronically>0</NotifyElectronically>

    <InactiveAttorney>false</InactiveAttorney>

    <PhoneNumber>123</PhoneNumber>

    <PhoneNumber>393</PhoneNumber>

    <HouseNumber>369</HouseNumber>

    </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?

  • 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

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

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