XML Parsing Problem

  • I need to parse the following XML file into columns and rows:

    <?xml version="1.0" encoding="UTF-8"?>
    <PartyID>
    <PartyID>147</PartyID>
    <CampaignID>
    <CampaignID>1</CampaignID>
    <Arc>A</Arc>
    <TicPosition>2</TicPosition>
    </CampaignID>
    <CampaignID>
    <CampaignID>1</CampaignID>
    <Arc>A</Arc>
    <TicPosition>13</TicPosition>
    </CampaignID>
    </PartyID>

    I am using this query:

    SELECT
    Data.Col.value('(./PartyID)[1]', 'int') As Party_ID,
    Data.Col.value('(./CampaignID)[1]' , 'int') As Campaign_ID,
    Data.Col.value('(./Arc)[1]', 'varchar(1)') As Arc,
    Data.Col.value('(./TicPosition)[1]', 'varchar(10)') As TicPosition
    FROM @UsageFile.nodes('/PartyID/CampaignID') As Data(Col)

    it produces this result

    Party_ID Campaign_ID Arc TicPosition

    NULL 1 A 2

    NULL 1 A 13

    How can I get the Party_ID value into the query results?

  • Replace

    Data.Col.value('(./PartyID)[1]', 'int') As Party_ID

    with

    Data.Col.value('(../PartyID)[1]', 'int') As Party_ID

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thank you Mark, this resolved my problem.

  • Note that the "./" in your code is not required based on the XML Context. Also note that specifying that you need the elements' text, via text(), will improve performance.

    For PartyID there are a couple ways to get what you want:

    DECLARE @UsageFile XML = 
    '<?xml version="1.0" encoding="UTF-8"?>
    <PartyID>
    <PartyID>147</PartyID>
    <CampaignID>
    <CampaignID>1</CampaignID>
    <Arc>A</Arc>
    <TicPosition>2</TicPosition>
    </CampaignID>
    <CampaignID>
    <CampaignID>1</CampaignID>
    <Arc>A</Arc>
    <TicPosition>13</TicPosition>
    </CampaignID>
    </PartyID>'

    SELECT
    Data.Col.value('(../PartyID)[1]', 'int') AS Party_ID,
    @UsageFile.value('(PartyID/PartyID)[1]','int') AS Party_ID_ALT,
    Data.Col.value('(CampaignID/text())[1]' , 'int') AS Campaign_ID,
    Data.Col.value('(Arc/text())[1]', 'varchar(1)') AS Arc,
    Data.Col.value('(TicPosition/text())[1]', 'varchar(10)') AS TicPosition
    FROM @UsageFile.nodes('/PartyID/CampaignID') As Data(Col);

     

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • This was removed by the editor as SPAM

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

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