XML Query to return xml element as column for specific attribute value

Question

XML Query to return xml element as column for specific attribute value

ratbak

Ten Centuries

Points: 1381
More actions
August 5, 2022 at 1:26 pm

#4072979
Despite having read several articles/examples, I can't solve what seems like it should be a simple problem -- How do I return an xml element value as a column for a specified value of an attribute in that same element. e.g.,
```
<programme>
    <length units="minutes">31</length> -- How I return this as LengthMinutes?
    <length units="seconds">1906</length> -- How I return this as LengthSeconds?
    <episode-num system="xmltv_ns">0.14.0</episode-num>
    <episode-num system="onscreen">15</episode-num>-- How I return this as OnScreenEpiNumber?
    <episode-num system="assetID">ra15</episode-num>
</programme>
```
Below is my test script -- note that I am first treating as nvarchar, stripping out xml & DOCTYPE elements due to errors, & then casting as xml (secondary problem):
```
DECLARE @xmlTable TABLE

    (

    XmlData XML NOT NULL

    );
```
--DECLARE @xml XML = '
DECLARE @xml1String NVARCHAR(MAX) = '
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE tv SYSTEM "xmltv.dtd">
<tv date="2022-08-04" source-info-name="Radio Times" generator-info-name="XMLTV" generator-info-url="http://membled.com/pwork/apps/xmltv/">
<channel id="1">
<display-name lang="en">Sample Network</display-name>
<url>www.SampleNetwork.com</url>
</channel>
<programme start="20220731100000 +0000" stop="20220731103146 +0000" channel="1476">
<title lang="en">Program 1</title>
<sub-title lang="en">Program 1: Pursuit in Texas</sub-title>
<icon src="https://samplenetwork.com/sample/program1.png" width="1920" height="1080" />
<icon src="https://samplenetwork.com/sample/program1_2.png" width="1920" height="1080" />
<desc lang="en">A man leads officers on a pursuit in Texas.</desc>
<category lang="en">Reality</category>
<category lang="en">True Crime</category>
<language>English</language>
<orig-language>English</orig-language>
<date>20220725</date>
<length units="minutes">31</length>
<length units="seconds">1906</length>
<episode-num system="xmltv_ns">0.14.0</episode-num>
<episode-num system="onscreen">15</episode-num>
<episode-num system="assetID">ra15</episode-num>
<credits>
</credits>
<video>
<present>yes</present>
<colour>yes</colour>
<quality>1920x1080</quality>
</video>
<audio>
<present>yes</present>
<stereo>stereo</stereo>
</audio>
<previously-shown/>
</programme>
<programme start="20220731103146 +0000" stop="20220731110125 +0000" channel="1476">
<title lang="en">Program 1</title>
<sub-title lang="en">Program 1: Pilot, Pursuit on Golf Course</sub-title>
<icon src="https://samplenetwork.com/samplenetwork/program1.png" width="1920" height="1080" />
<icon src="https://samplenetwork.com/samplenetwork/program1_2.png" width="1920" height="1080" />
<desc lang="en">Introducing Program 1! Here you will find an immersive half-hour program!</desc>
<category lang="en">Reality</category>
<category lang="en">True Crime</category>
<language>English</language>
<orig-language>English</orig-language>
<date>20220711</date>
<length units="minutes">29</length>
<length units="seconds">1779</length>
<episode-num system="xmltv_ns">0.0.0</episode-num>
<episode-num system="onscreen">1</episode-num>
<episode-num system="assetID">ra1</episode-num>
<credits>
</credits>
<video>
<present>yes</present>
<colour>yes</colour>
<quality>1920x1080</quality>
</video>
<audio>
<present>yes</present>
<stereo>stereo</stereo>
</audio>
<premiere/>
</programme>
<programme start="20220731110125 +0000" stop="20220731113048 +0000" channel="1476">
<title lang="en">Program 1</title>
<sub-title lang="en">Program 1: Toddler Rescued From Fire</sub-title>
<icon src="https://samplenetwork.com/sample/program1.png" width="1920" height="1080" />
<icon src="https://samplenetwork.com/sample/program1_2.png" width="1920" height="1080" />
<desc lang="en">Officers team up with firefighters.</desc>
<category lang="en">Reality</category>
<category lang="en">True Crime</category>
<language>English</language>
<orig-language>English</orig-language>
<date>20220725</date>
<length units="minutes">29</length>
<length units="seconds">1762</length>
<episode-num system="xmltv_ns">0.12.0</episode-num>
<episode-num system="onscreen">13</episode-num>
<episode-num system="assetID">ra13</episode-num>
<credits>
</credits>
<video>
<present>yes</present>
<colour>yes</colour>
<quality>1920x1080</quality>
</video>
<audio>
<present>yes</present>t5
<stereo>stereo</stereo>
</audio>
<previously-shown/>
</programme>
</tv>
';
-- xml UTF-8 declaration causes parsing problems:
-- XML parsing: line 2, character 6, text/xmldecl not at the beginning of input
-- dtd declarations causes encoding/conversion problems:
-- Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support`
DECLARE @xml1 XML = CAST(REPLACE(@xml1String,N'<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE tv SYSTEM "xmltv.dtd">
',N'') AS XML);
INSERT INTO @xmlTable (XmlData)
VALUES(@xml1);
--SELECT * FROM @xmlTable;
-- Returns cartesian product
SELECT
channel.x.value('@id','int') AS ChannelId,
channel.x.value('(display-name/text())[1]','varchar(50)') AS ChannelDisplayName,
channel.x.value('(url/text())[1]','varchar(255)') AS ChannelUrl,
CONVERT(DATETIME2(0), STUFF(STUFF(STUFF(LEFT(scheduleEvent.x.value('@start','char(20)'),14), 9, 0, ' '), 12, 0, ':'), 15, 0, ':'),120) AS StartDateTime,
scheduleEvent.x.value('(title/text())[1]','varchar(120)') AS Title,
scheduleEvent.x.value('(sub-title/text())[1]','varchar(150)') AS SubTitle,
scheduleEvent.x.value('(category/text())[1]','varchar(60)') AS Category,
scheduleEvent.x.value('(category/text())[2]','varchar(60)') AS Category2,
scheduleEvent.x.value('(language/text())[1]','varchar(60)') AS Language,
scheduleEvent.x.value('(orig-language/text())[1]','varchar(60)') AS OriginalLanguage,
scheduleEvent.x.value('(length/text())[1]','int') AS DurationMinutes, -- Length,
-- \/ Returns null... How do you do this with attribute on the same element?!? \/
XmlData.value('(/tv/programme[@units="minutes"]/length/text())[1]','int') AS LengthMinutes,
scheduleEvent.x.value('(length/text())[2]','int') AS DurationSeconds, -- Length2,
--/Officers/Colonel[@id = "2"]
--ProductXML.query(‘/root/product[id=sql:variable("@product_id")]/description’)
--@x.query('/fantasy/player[@Position="C"]/team')
--scheduleEvent.x.query('length([@units="minutes")]/length'),
--scheduleEvent.x.query('/length/[contains(@units, "minutes")]') AS ProgramMinutes
--scheduleEvent.x.query('/length[contains(@units, "@")="minutes"=true()]') AS DurationMinutes,
--scheduleEvent.x.value('length[@units="minutes"])','int') AS DurationMinutes,
--scheduleEvent.x.value('length[@units="seconds"])','int') AS DurationSeconds
scheduleEvent.x.value('(episode-num/text())[2]','int') AS EpisodeNumberOnScreen
--CAST(CASE scheduleEvent.x.value('(video/present/text())[1]','varchar(3)')
-- WHEN 'yes' THEN 1
-- WHEN 'no' THEN 0
-- ELSE NULL END AS BIT) AS HasVideo,
--scheduleEvent.x.value('(video/quality/text())[1]','varchar(30)') AS VideoQuality,
--CAST(CASE scheduleEvent.x.value('(audio/present/text())[1]','varchar(3)')
-- WHEN 'yes' THEN 1
-- WHEN 'no' THEN 0
-- ELSE NULL END AS BIT) AS HasAudio
FROM @xmlTable xmlTable
CROSS APPLY XmlData.nodes('/tv/channel') AS channel(x)
CROSS APPLY XmlData.nodes('/tv/programme') AS scheduleEvent(x)
--CROSS APPLY XmlData.nodes('/tv/programme/length') AS program(length)
</pre>
For bonus points, how do I get this to work with the xml & DOCTYPE elements included?
xml UTF-8 declaration causes parsing problems:
XML parsing: line 2, character 6, text/xmldecl not at the beginning of input
dtd declarations causes encoding/conversion problems:
Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support`

Viewing 3 posts - 1 through 2 (of 2 total)

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

Arthur Olcot SSCertifiable Points: 6651 More actions · Answer 1

Hi,

You can achieve this by using a predicate to target the attributes, here is something that will hopefully get you on the right track:

DECLARE @xml XML = '
<programme>
    <length units="minutes">31</length> -- How I return this as LengthMinutes?
    <length units="seconds">1906</length> -- How I return this as LengthSeconds?
    <episode-num system="xmltv_ns">0.14.0</episode-num>
    <episode-num system="onscreen">15</episode-num>-- How I return this as OnScreenEpiNumber?
    <episode-num system="assetID">ra15</episode-num>
</programme>'


SELECT @xml.value('(/programme/length[@units="minutes"]/text())[1]', 'int') LengthMinutes
, @xml.value('(/programme/length[@units="seconds"]/text())[1]', 'int') LengthSeconds
, @xml.value('(/programme/episode-num[@system="onscreen"]/text())[1]', 'int') OnScreenEpiNumber

This reply was modified 2 years, 4 months ago by Arthur Olcot. Reason: typo in column name

ratbak Ten Centuries Points: 1381 More actions · Answer 2

That worked. I don't know why I struggled so much getting that syntax right.

Thank you, Arthur!