XML Parsing Problem

Question

Post reply

XML Parsing Problem

SQLJim1

Old Hand

Points: 341

May 3, 2021 at 2:11 pm

#3878772

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?

This topic was modified 3 years, 6 months ago by Steve Jones - SSC Editor. Reason: code formatting

May 6, 2021 at 7:04 am

This was removed by the editor as SPAM

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

This might also help: https://www.sqlservercentral.com/steps/stairway-to-xml-level-4-querying-xml-data

SQLJim1 Old Hand Points: 341 More actions · Answer 3

SQLJim1

Old Hand

Points: 341

May 4, 2021 at 4:09 am

#3878998

Thank you Mark, this resolved my problem.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

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