Blog Post

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating