Basic XML Queries–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question recently on querying an XML document. While I think XML is a pain and it’s not the future, there is a lot of it out there that you might need to deal with in a database. Legacy stuff will be there for awhile.

In any case, someone was struggling with this code.

DECLARE @x 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,
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 @x.nodes('/PartyID/CampaignID') As Data(Col)

The person got results where the Party_ID was NULL. Some of you might get what’s wrong, but it’s a question of understanding your context.

In this case, the FROM clause helps us understand this. When we specify the node() method, we choose a path in the document. The path we pick is PartyID/CampaignID. This puts us here in the document:

    <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>2</TicPosition>
     </CampaignID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>13</TicPosition>
     </CampaignID>

If we are trying to specify paths on the current position with the period (.), we can only see these values. There is no PartyID here.

However, similar to a folder navigation from the command line, if I use two periods (..), I move up one level. From here, I can get the PartyID. Therefore, my code is:

2021-05-03 11_15_57-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (59))_ - Microsoft SQL Server

SQLNewBlogger

As soon as I saw this question, I knew the issue. It was a good reminder to me to watch the path, which is why I thought this was a good thing to post about. It cements this in my memory.

In 10 minutes, I did this, just as you could.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.