CODEWITHSUNDEEP
sql-servert-sqlxquery
Mark Roworth
Mark Roworth

Reputation: 566

Using TSQL and XQuery to extract values from XML

I have a table with an XML column. Some of the XML is very large (8MB) but I'll present a simpler version of the problem here. Overall, I need to update the table and find those rows where the XML contains a node named <CompressedPart> at a known point in the XML tree, take its value, base64-decode it and replace <CompressedPart> with the resulting data.

This question is simply just the first part of that, which is trying to extract the text under a point in the XML tree. I've encountered XQuery once before and it just as life-destroying as it appears to be now.

To this end, I've simplified the XML to just two nodes thus:

<GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
    <EnvelopeVersion>2.0</EnvelopeVersion>
</GovTalkMessage>

and I'm simply trying to get the value "2.0". The code I'm using is:

SELECT  CAST('<GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
            <EnvelopeVersion>2.0</EnvelopeVersion>
        </GovTalkMessage>' AS XML).value('(/GovTalkMessage/EnvelopeVersion)[1]', 'VARCHAR(MAX)')

but this returns NULL. I've tried removing/adding forward slashes, removing the [1] (which gives the incredible un-useful error message "requires a singleton"). Whatever I specify in the XQuery I just get NULL or an error.

In time I will want to select across the whole table, as below, so I'm not just looking for a solution that works for a single XML variable in the FROM clause as I've seen in other examples. This type of thing:

SELECT  GOVTALK_XML_INPUT_DATA.value('(/GovTalkMessage/EnvelopeVersion)[1]', 'VARCHAR(MAX)')
FROM    dbo.IndividualSubmission

How do I go about querying to solve just this first part of my issue?

Upvotes: 0

Views: 151

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

A couple ways..

DECLARE @X XML = '
<GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
    <EnvelopeVersion>2.0</EnvelopeVersion>
</GovTalkMessage>';

SELECT @X.value('(//*:EnvelopeVersion/text())[1]', 'varchar(20)');

Or.. 

DECLARE @X VARCHAR(1000) = '
<GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
    <EnvelopeVersion>2.0</EnvelopeVersion>
</GovTalkMessage>';

SELECT CAST(@X AS XML).value('(//*:EnvelopeVersion/text())[1]', 'varchar(20)');

Upvotes: 1

Related Questions