August 5, 2022 at 1:26 pm
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`
August 5, 2022 at 1:50 pm
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
August 5, 2022 at 2:34 pm
That worked. I don't know why I struggled so much getting that syntax right.
Thank you, Arthur!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy