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

  • 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`

     
  • 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 1 week, 1 day ago by  Arthur Olcot. Reason: typo in column name
  • That worked. I don't know why I struggled so much getting that syntax right.

    Thank you, Arthur!

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

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