Hi all...Searched but could not find an example...

BTW...Thanks for the '(title/text())[1]' text() hint from another post...Really improved query performance...

Excerpt from XML field...

<programs>
<program TMSId="MV000004570000" connectorId="MV000004570000">
<titles>
<title size="120" type="full" lang="en">Suspicion</title>
</titles>
<descriptions>
<desc size="250" type="plot" lang="en">Alfred Hitchcock directs a thriller about a woman who thinks that her husband is plotting to murder her.</desc>
<desc size="100" type="plot" lang="en">An English heiress (Joan Fontaine) fears that her playboy husband (Cary Grant) plans to kill her.</desc>
<desc size="60" type="plot" lang="en">A woman fears that her husband is a murderer.</desc>
</descriptions>
<runTime>PT01H30M</runTime>
<progType>TV Movie</progType>
</program>

I am able to get the program, title and description data...Both value data and attribute data...My problem is getting data at an element level with no subelements...

In this example, it's runTime and progType as well as others...

I have the following query...I've tried accessing with both of the following...

xmlProgType.value('(progType/text())[1]','varchar(10)')

and

xmlProgType.value('@progType','varchar(10)')

SELECT
--xmlProgram.query('.')
xmlProgram.value('@TMSId','varchar(15)'),
xmlProgram.value('@altFilmId','int'),
xmlProgram.value('@rootId','int'),
xmlProgram.value('@versionId','int'),
xmlProgram.value('@connectorId','varchar(15)'),
xmlTitle.value('(title/text())[1]','varchar(100)'),
xmlTitle.value('(title/text())[2]','varchar(100)'),
xmlTitle.value('(title/text())[3]','varchar(100)'),
RTRIM(xmlDescription.value('(desc/text())[1]','varchar(250)')) +
CASE RIGHT(xmlDescription.value('(desc/text())[1]','varchar(250)'),1)
WHEN '.' THEN ' ' ELSE '. '
END +
RTRIM(xmlDescription.value('(desc/text())[2]','varchar(250)')) +
CASE RIGHT(xmlDescription.value('(desc/text())[2]','varchar(250)'),1)
WHEN '.' THEN ' ' ELSE '. '
END +
RTRIM(xmlDescription.value('(desc/text())[3]','varchar(250)') +
CASE RIGHT(xmlDescription.value('(desc/text())[3]','varchar(250)'),1)
WHEN '.' THEN ' ' ELSE '. '
END
) AS Description,
xmlTitleAtt.value('@size','int'),
xmlTitleAtt.value('@type','varchar(5)'),
xmlTitleAtt.value('@lang','varchar(5)'),
xmlProgType.value('(progType/text())[1]','varchar(10)')
FROM
Tribune_XML_Import_BaseDoc
CROSS APPLY
Tribune_XML_Import_BaseDoc_Data.nodes('//programs/program') tmpXMLProgram(xmlProgram)
CROSS APPLY
xmlProgram.nodes('titles') tmpXMLTitle(xmlTitle)
CROSS APPLY
xmlTitle.nodes('title') tmpXMLTitleAtt(xmlTitleAtt)
CROSS APPLY
xmlProgram.nodes('descriptions') tmpXMLDescription(xmlDescription)
OUTER APPLY
xmlProgram.nodes('progType') tmpXMLProgType(xmlProgType)


It returns the field but with NULL values...

Thanks in advance...

KLC