SQL Server Performance

Help with XQuery

Discussion in 'SQL Server 2008 General Developer Questions' started by L0st_Pr0phet, Jul 8, 2010.

  1. L0st_Pr0phet Member

    I am trying to interrogate a xml field I am trying to use the below code:
    select convert(xml,a.manifest).query('/Outputs/* ')
    as result,a.manifest
    from DSG_Live.dbo.tbltasks a
    where a.isOrderline = 1
    and a.tsOrdertypeId in (11,18)
    and a.task_id > 84471
    and( a.workorderno = '1140171' And a.WorkOrderSeqNo = '1')
    I am trying to get the aspectratio field in the output section of the manifest which I have tagged below, I just keep getting blank. Can anyone help?
    the manifest is below:
    <BBCWWOrder xmlns="http://www.ascentmedia.co.uk/BBCWWOrder.xsd"><ClientOrderId>e9501272-8f2f-42c1-875c-ebddec447063</ClientOrderId><TaskType>BBCWWOrderLine</TaskType><Status>100</Status><LastOperationDate>17/06/2010 13:08:43</LastOperationDate><ChargeCode>5685</ChargeCode><JobReference>TC00314023/01</JobReference><ContactEmail>robin.jacobs@bbc.co.uk</ContactEmail><OrderDetails><OrderLine xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="a66989f0-e9ed-496f-843c-b79c8abea508" Priority="Medium" DeliveryDue="2010-06-17T00:00:00+01:00" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Order/2.0.0"><Inputs><Input ID="a578a56d-517a-4e96-b15d-041e03108049"><Ordinal>0</Ordinal><Item xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/ProductCatalogue/2.0.0"><EntryIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Catalogue/2.0.0"><ServiceIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">BBCWW</ServiceIdentifier><LocalIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">ABKB312J~01</LocalIdentifier></EntryIdentifier><Ordinal xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Catalogue/2.0.0">0</Ordinal><Name xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Catalogue/2.0.0">EP 01</Name><Description xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Catalogue/2.0.0">In 1969 The Rolling Stones found themselves forced out of the UK for tax reasons and suddenly short of cash, after falling out with their manager Alan Klein. The band rented a villa near Cap D'Antibes and they all decamped to the Cote D'Azur in south-east France. Joining them was an extraordinary entourage of beautiful women, musicians, technicians, drug dealers and addicts and rogue French chefs. It was among this bizarre collection of people, deep in the basement of the building, that an album of songs started to come together, normally somewhere between 2am and 5am each morning. This amazing film looks back over that exuberant period of scandal and creativity. Newly shot interviews with the band and a selection of the cast of characters from the time help to shed light on what it was like to be in, or around the band during those hedonistic days, while archive footage, tour outtakes and candid photographs recreate the rock and roll era.</Description><Parents xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Catalogue/2.0.0"><Entry xmlns:q1="http://services.worldwide.bbc.co.uk/schema/enterprise/type/ProductCatalogue/2.0.0" xsi:type="q1:SKU"><EntryIdentifier><ServiceIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">BBCWW</ServiceIdentifier><LocalIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">ABKB312J</LocalIdentifier></EntryIdentifier><Ordinal>0</Ordinal><Name>STONES IN EXILE</Name><Description>In 1969, The Rolling Stones found themselves forced out of the UK for tax reasons and suddenly short of cash, after falling out with their manager Alan Klein. The band rented a villa near Cap d'Antibes and they all decamped to the Cote d'Azur in south-east France. Joining them was an extraordinary entourage of beautiful women, musicians, technicians, drug dealers and addicts and rogue French chefs. It was among this bizarre collection of people, deep in the basement of the building, that an album of songs started to come together, normally somewhere between 2am and 5am each morning. This amazing film looks back over that exuberant period of scandal and creativity. Newly shot interviews with the band and a selection of the beautiful people and bohemians from the time help to shed light on what it was like to be in or around the band during those hedonistic days, while previously unseen archive footage, tour out-takes and candid photographs re-create the Sixties era of rock and excess.</Description><Parents><Entry><EntryIdentifier><ServiceIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">BBCWW</ServiceIdentifier><LocalIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">ABKB312J~01</LocalIdentifier></EntryIdentifier><Ordinal>0</Ordinal></Entry></Parents><Children><Entry><EntryIdentifier><ServiceIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">BBCWW</ServiceIdentifier><LocalIdentifier xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0">ABKB312J~01</LocalIdentifier></EntryIdentifier><Ordinal>0</Ordinal></Entry></Children><q1:primarySubgenre>Music &amp; Arts</q1:primarySubgenre><q1:Subgenres><Genre>Music &amp; Arts</Genre></q1:Subgenres><q1:WorkingTitles><q1:WorkingTitle>ROLLING STONES - EXILE ON MAIN STREET</q1:WorkingTitle></q1:WorkingTitles><q1:Contributors><q1:Contributor><q1:Forename>Charlie</q1:Forename><q1:Surname>Watts</q1:Surname><q1:Roles><q1:Role>Executive Producer</q1:Role></q1:Roles></q1:Contributor><q1:Contributor><q1:Forename>John</q1:Forename><q1:Surname>Battsek</q1:Surname><q1:Roles><q1:Role>Producer</q1:Role></q1:Roles></q1:Contributor><q1:Contributor><q1:Forename>Keith</q1:Forename><q1:Surname>Richards</q1:Surname><q1:Roles><q1:Role>Executive Producer</q1:Role></q1:Roles></q1:Contributor><q1:Contributor><q1:Forename>Mick</q1:Forename><q1:Surname>Jagger</q1:Surname><q1:Roles><q1:Role>Executive Producer</q1:Role></q1:Roles></q1:Contributor></q1:Contributors><q1:propertyBag><Property Name="SALES_STATUS_DESCRIPTION" Value="AVAILABLE FOR GENERAL SALE " xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="SALES_STATUS" Value="PR/S/IU" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="STRAND_DESCRIPTION" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="Product_Classification_Code" Value="L" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="Product_Classification_Description" Value="Invested/indie " xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /></q1:propertyBag></Entry></Parents><PartNumber>1</PartNumber><Duration>60:00</Duration><Contributors><Contributor><Forename>Charlie</Forename><Surname>Watts</Surname><Roles><Role>Executive Producer</Role></Roles></Contributor><Contributor><Forename>John</Forename><Surname>Battsek</Surname><Roles><Role>Producer</Role></Roles></Contributor><Contributor><Forename>Keith</Forename><Surname>Richards</Surname><Roles><Role>Executive Producer</Role></Roles></Contributor><Contributor><Forename>Mick</Forename><Surname>Jagger</Surname><Roles><Role>Executive Producer</Role></Roles></Contributor></Contributors><PropertyBag><Property Name="STRAND_DESCRIPTION" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="CORENUMBERS" Value="" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="YEAR_INVESTED" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /></PropertyBag></Item><Specification><TVStandard xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">PAL</TVStandard><AspectRatio xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">16x9 FHA</AspectRatio><FrameWidth xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">720</FrameWidth><FrameHeight xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">608</FrameHeight><FrameRate xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">25fps</FrameRate><VideoCodec xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><VideoCompressionMode>Constant Bit Rate</VideoCompressionMode><VideoBitrate>50000</VideoBitrate><MPEG2><MPEG2StreamType>ProgramStream</MPEG2StreamType><FrameStructure>I</FrameStructure></MPEG2></VideoCodec><AudioCodec xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">PCM</AudioCodec><AudioBitrate xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">1920</AudioBitrate><AudioSampleFrequency xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">48khz</AudioSampleFrequency><AudioChannels Count="4" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><AudioChannel Ordinal="1" Type="Master Left" /><AudioChannel Ordinal="2" Type="Master Right" /><AudioChannel Ordinal="3" Type="M and E Left" /><AudioChannel Ordinal="4" Type="M and E Right" /></AudioChannels><PictureOption xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">Clean (suitable for texted sales)</PictureOption><Language xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><Code>ENG</Code><Name>ENGLISH</Name></Language><MediaWrapper xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">MPG</MediaWrapper><AudioCompressionMode xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">Constant Bit Rate</AudioCompressionMode><PictureSampling xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">4:2:2</PictureSampling><TechnicalReport xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><Section Name="Bars and Tone" StartPoint="09:59:00:00" /><Section Name="Clean Elements" StartPoint="11:02:00:00" EndPoint="11:10:39:08" /><Section Name="Programme Content" StartPoint="10:00:00:00" EndPoint="11:00:48:24" /></TechnicalReport></Specification><Media Quantity="0"><Asset xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0">Master</Asset><Location Type="Archive" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0">ASCENT</Location><Parts xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0"><Part ID="f9ca39a3-227a-47cd-a366-8cd7e903ed3b" Ordinal="1" Duration="01:00:00" /></Parts></Media></Input></Inputs><Outputs><Output ID="bf89c9f4-dcc0-4dda-af94-349f9f6068b0" Name="EP 01"><Specification><TVStandard xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">NTSC</TVStandard>
    <AspectRatio xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">14x9 LB</AspectRatio>
    <FrameWidth xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">720</FrameWidth><FrameHeight xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">512</FrameHeight><FrameRate xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">30fps</FrameRate><AudioChannels Count="4" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><AudioChannel Ordinal="1" Type="Master Left" /><AudioChannel Ordinal="2" Type="Master Right" /><AudioChannel Ordinal="3" Type="M and E Left" /><AudioChannel Ordinal="4" Type="M and E Right" /></AudioChannels><PictureOption xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0">Clean (suitable for texted sales)</PictureOption><Language xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Specification/2.0.0"><Code>ENG</Code><Name>ENGLISH</Name></Language></Specification><Media Quantity="1"><Tape xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0">BetaSP</Tape><Location Type="Address" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0">Robin Jacobs BBC WORLDWIDE LTD, Worldwide Media Operations, Room WC G371, White City Building, 201 Wood Lane, London. W12 7TQ</Location><Parts xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Media/2.0.0"><Part ID="1ab37f50-e3e6-4c2a-92f4-679eb3898b9e" Ordinal="0" /></Parts></Media><AssetClassifier>STAR</AssetClassifier></Output></Outputs><CurrentStatus ID="a66989f0-e9ed-496f-843c-b79c8abea508" Status="Pending"><AuditInformation><User>Robin Jacobs</User><System>Star</System><UTCTimeStamp>2010-06-15T08:39:04.4226661Z</UTCTimeStamp></AuditInformation></CurrentStatus><AuditInformation><User>Robin Jacobs</User><System>Star</System><UTCTimeStamp>2010-06-15T09:39:00.4070411+01:00</UTCTimeStamp></AuditInformation><AlternativeIdentifiers><AlternativeIdentifier><Site>COS Orderline Id</Site><Identifier>d4acda48-5527-42bf-a2d8-7bbe4a4a440e</Identifier></AlternativeIdentifier></AlternativeIdentifiers><PropertyBag><Property Name="InputClassifier" Value="episode" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="Ceefax" Value="False" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /><Property Name="Notes" Value="HI NOREEN,&#xA;&#xA;ANY CHANCE THIS COULD BE DONE BY THURSDAY PLEASE?&#xA;&#xA;THANKS&#xA;ROBIN AudioTrack 1: UNKNOWN, AudioTrack 2: UNKNOWN, AudioTrack 3: UNKNOWN, AudioTrack 4: UNKNOWN" xmlns="http://services.worldwide.bbc.co.uk/schema/enterprise/type/Base/2.0.0" /></PropertyBag></OrderLine></OrderDetails></BBCWWOrder>
  2. Luis Martin Moderator

    Could you find any solution?
  3. satya Moderator

    I doubt the XML is not loaded properly which returns blank value, see the example from BOL on same lines:
    DECLARE @DocHandle int
    DECLARE @XmlDocument nvarchar(1000)
    SET @XmlDocument = N'<ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
    <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
    OrderDate="1996-07-04T00:00:00">
    <OrderDetail ProductID="11" Quantity="12"/>
    <OrderDetail ProductID="42" Quantity="10"/>
    </Order>
    </Customer>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
    <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
    OrderDate="1996-08-16T00:00:00">
    <OrderDetail ProductID="72" Quantity="3"/>
    </Order>
    </Customer>
    </ROOT>'
    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
    WITH (CustomerID varchar(10),
    ContactName varchar(20))
    EXEC sp_xml_removedocument @DocHandle

Share This Page