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:
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.
Voice of the DBA 