Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 64 – Reading values from an XML column

Jul 18 2011 2:19AM by Jacob Sebastian   

I got a question in my personal forum this morning requesting help to read values from an XML column. My first reaction was “Well, there is an XQuery lab demonstrating this!”. However, after reviewing the existing XQuery labs, I realized there are no posts demonstrating this.

Here is a simple example that demonstrates how to read values from an XML column.

DECLARE @t TABLE (
	ID INT IDENTITY,
	Data XML
)

INSERT INTO @t (Data)
SELECT '<employee name="Jacob" />' UNION ALL
SELECT '<employee name="Michael" />'

SELECT
	x.value('@Name[1]', 'VARCHAR(20)') AS Name
FROM @t t
CROSS APPLY Data.nodes('/employee') a(x)
/*
Name
--------------------
Jacob
Michael
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Tags: XML, XQuery, SQL SERVER, BRH, #SQL Server,


Jacob Sebastian
1 · 100% · 32235
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

14  Comments  

  • the code is nice but the result might be nothing since it is a case-sensitive. i think the code should be :

    SELECT x.value('@[1]name[1][1]', 'VARCHAR(20)') AS Name FROM @t t CROSS APPLY Data.nodes('/[1]employee[1]') a(x)

    please correct me if i'm wrong. i'm new in xquery too.

    commented on Feb 22 2012 4:14AM
    Epon
    2844 · 0% · 3
  • You are right. I just corrected it.

    Thank for catching this.

    commented on Feb 22 2012 4:38AM
    Jacob Sebastian
    1 · 100% · 32235
  • sir i m working on xquery for finding assocation rule using xquery i complete the conding for how i write xquery for that. but now i found a problem that they give me error that "Unexpected token "declare function" beyond end of query" i send me coding part for u plz help me out for how i do.........i m using oxygen xml developer for running the xquery my simple query running proper but we use more than one function in recursive manner they create problem give error Unexpected token "declare function" beyond end of query.

    (: You can activate the content completion by pressing the Ctrl+Space keys. :)
    xquery version "1.0";
    
    (: Namespace for the <oXygen/> custom functions and variables :)
    declare namespace oxy="http://www.oxygenxml.com/xquery/functions";
    declare namespace global="http://www.oxygenxml.com/xquery/functions";
    (: The URI of the document that is to be queried :)
    declare variable $oxy:document-uri as xs:string := "transaction.xml";
    declare variable $oxy:document-to-query as xs:string := "transaction.xml";
    declare variable $local:document-to-query as xs:string := "transaction.xml";
    declare variable $oxy:document as document-node() := doc($oxy:document-uri);
    (:declare function Fp-growth($l,$L,$minsup,$total,$src) as xs:(element,element,minsup,total,src) := "transaction.xml";:)
    
    let $src := doc("transaction.xml")//items
    let $minsup:=2
    let $total:=count($src)*1.00
    let $c:=distinct-values($src/*)
    let $l:=(for $itemset in $c
    let $items:=(for $item in $src/*
    where $itemset=$item
    return $item)
    let $sup:=(count($items)+1)
    where $sup>=$minsup
    return <largeItemset>
    <items> {$items} </items>
    <support> {$sup} </support>
    </largeItemset>)
    let $L:=$l
    return <largeItemsets>{oxyl:FP-growth($l,$L,$minsup,$total,$src)}
    </largeItemsets>
    _/////that part is fine but we we use next fp-growth function they give same error
    
    plz sir help me for solving this////
    
    
    declare function oxy:FP-growth(element $l, element $L, element $minsup, element $total, element $src)
    returns element {
    let $f-item:= first item in $L
    let $l-item:= last item in $L
    let $T:=oxy:getl-itemsets($src, $total, $minsup)
    return <items>
    {oxy:join ($l-item, $T)}
    </items>
    let $l:=$l-items
    let $L:=oxy:remove($l)
    for f-item in $L
    where $f-item != $l-item
    return oxy:FP-growth($l, $L, $minsup, $total, $src)
    }
    
    declare function oxy:getl-itemsets(element $src, element $total, element $minsup)
    returns element {
    let $l:=(for $itemset in $src
    where $itemset=$l-item
    return $itemset)
    let $sup:=(count($l)+1
    where $sup>=$minsup
    return <largeItemset>
    <items> {$item} </items>
    <support> {$sup} </support>
    </largeItemset>
    }
    
    declare function oxy:join(element $X, element $Y) returns element {
    let $items := (for $item in $Y
    where every $i in $X satisfies
    $i != $item
    return $item)
    return $X union $items
    }
    
    declare function oxy:removeDuplicate(element $C) returns element
    {
    for $itemset1 in $C
    let $items1 := $itemset1/*
    let $items :=(for $itemset2 in $C
    let $items2 := $itemset2/*
    where $itemset2>>$itemset1 and
    count($items1) =
    count(commonIts($items1, $items2))
    return $items2)
    where count($items) = 0
    return $itemset1
    }
    
    
    input file for this xquery is:=
    <?xml version="1.0" encoding="UTF-8"?>
    <?xml-stylesheet type="text/css" href="personal-schema.css"?>
    <transactions>
    <transaction id="1">
    <items>
    <title>The Green Mile</title>
    <author>Stephen King</author>
    <item>a</item>
    <item>d</item>
    <item>e</item>
    </items>
    </transaction>
    <transaction id="2">
    <items>
    <title>The Green Mile</title>
    <author>J. D. Salinger</author>
    <item>b</item>
    <item>c</item>
    <item>d</item>
    </items>
    </transaction>
    <transaction id="3">
    <items>
    <title>The Green Mile</title>
    <author>Issac Asimov</author>
    <item>a</item>
    <item>c</item>
    <item>e</item>
    </items>
    </transaction>
    <transaction id="4">
    <items>
    <title>The Green Mile</title>
    <author>Isaac Asimov</author>
    <item>b</item>
    <item>c</item>
    <item>d</item>
    </items>
    </transaction>
    <transaction id="5">
    <items>
    <title>The Green Mile</title>
    <author>Oscar Wilde</author>
    <item>a</item>
    <item>b</item>
    
    </items>
    </transaction>
    </transactions>
    
    commented on Mar 13 2012 4:23AM
    malikvnit
    3070 · 0% · 2
  • Unfortunately, I am not familiar with the environment you mentioned. My interaction with XML is mostly in and around SQL server.

    commented on Mar 13 2012 4:41AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi,

    I was wondering if you could help me with my first every xquery. I have an XML datatype column in my database and I need to break it up into it's various columns. This is the xml in the column:

    <pluginState xmlns:xsi=".....">
      <guid op="add">88BEA6EB-10BC-493E-EE8C-69ED0CED19F1</guid>
      <beyondblue op="add">
        <guid op="add">6BD1DB3E-B64C-E56C-9CF0-639E8ADCAB04</guid>
        <managementPlan op="add">
          <guid op="add">F5E1BED4-9CAE-3E4F-A2D6-F31472FB8E7B</guid>
          <interventions op="add">Removed access to means</interventions>
          <interventions op="add">Diversionary activity given</interventions>
          <coldReferral op="add">Gambling Service</coldReferral>
          <actions op="add">Lodge Child Protection notification</actions>
          <actions op="add">Transferred to head office/resource fulfilment</actions>
          <warmReferral op="add">Other Service</warmReferral>
        </managementPlan>
      </beyondblue>
    </pluginState>
    

    I need to break it up into: Interventions| ColdReferral|Actions|warmreferral.

    Is that possible?

    Thanks for your help in advance.

    Emer

    commented on Dec 10 2012 10:54PM
    Emer
    2219 · 0% · 6
  • does this help?

    DECLARE @t TABLE (data XML)
    INSERT INTO @t (data) SELECT '
    <pluginState xmlns:xsi="www.example.com">
      <guid op="add">88BEA6EB-10BC-493E-EE8C-69ED0CED19F1</guid>
      <beyondblue op="add">
    	<guid op="add">6BD1DB3E-B64C-E56C-9CF0-639E8ADCAB04</guid>
    	<managementPlan op="add">
    	  <guid op="add">F5E1BED4-9CAE-3E4F-A2D6-F31472FB8E7B</guid>
    	  <interventions op="add">Removed access to means</interventions>
    	  <interventions op="add">Diversionary activity given</interventions>
    	  <coldReferral op="add">Gambling Service</coldReferral>
    	  <actions op="add">Lodge Child Protection notification</actions>
    	  <actions op="add">Transferred to head office/resource fulfilment</actions>
    	  <warmReferral op="add">Other Service</warmReferral>
    	</managementPlan>
      </beyondblue>
    </pluginState>'
    
    SELECT
    	x.value('guid[1]', 'VARCHAR(32)') as GUID,
    	x.value('interventions[1]', 'VARCHAR(20)') as interventions1,
    	x.value('interventions[2]', 'VARCHAR(20)') as interventions2,
    	x.value('coldReferral[1]', 'VARCHAR(20)') as coldReferral,
    	x.value('warmReferral[1]', 'VARCHAR(20)') as warmReferral
    FROM @t t
    CROSS APPLY data.nodes('/pluginState/beyondblue/managementPlan') a(x)	
    
    /*
    GUID          interventions1    interventions2   coldReferral     warmReferral
    ------------- ----------------- ---------------- ---------------- -------------
    F5E1BED4-9... Removed access... Diversionary ... Gambling Service Other Service
    */
    
    commented on Dec 11 2012 5:16AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob,

    Thank you for your help I really appreciate it. It does help a lot and that query runs perfectly for me as well. When I try to run it directly from the column in the table I have it comes back blank. I alter the query to reflect my table (EVENT) reading from the column (PLUGIN_XML). Can you help me in where I am going wrong with this?

    SELECT
    x.value('guid[1]', 'VARCHAR(32)') as GUID,
    x.value('interventions[1]', 'VARCHAR(20)') as interventions1,
    x.value('interventions[2]', 'VARCHAR(20)') as interventions2,
    x.value('coldReferral[1]', 'VARCHAR(20)') as coldReferral,
    x.value('warmReferral[1]', 'VARCHAR(20)') as warmReferral
    FROM dbo.EVENT t
    CROSS APPLY PLUGIN_XML.nodes('/pluginState/beyondblue/managementPlan') a(x)
    

    Thank you.

    commented on Dec 11 2012 3:40PM
    Emer
    2219 · 0% · 6
  • hard to say without looking at the data. Please do the following.

    1. Post the create script of your table
    2. Post an INSERT query that inserts ONE real row from your table into the table

    Send me both. I will recreate the table, insert your real data and then see why the query is not returning value.

    commented on Dec 12 2012 4:44AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob,

    I am a lot closer now to a solution thank you for your help thus far. I believe I am having trouble because I have a default namespace. Here are the steps you require:

    CREATE TABLE [dbo].[EVENT](
        [EVENT_ID] [int] IDENTITY(1,1) NOT NULL,
        [PLUGIN_STATE_DELTA_XML] [xml] NULL,
    )
    
    insert into dbo.EVENT
            ( 
               PLUGIN_STATE_DELTA_XML
            )
    values  (
        		'<pluginState xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.medibankhealth.com.au" xsi:schemaLocation="http://www.medibankhealth.com.au AladdinPluginState.xsd">
      <guid op="add">88BEA6EB-10BC-493E-EE8C-69ED0CED19F1</guid>
      <beyondblue op="add">
        <guid op="add">6BD1DB3E-B64C-E56C-9CF0-639E8ADCAB04</guid>
        <managementPlan op="add">
          <guid op="add">F5E1BED4-9CAE-3E4F-A2D6-F31472FB8E7B</guid>
          <interventions op="add">Removed access to means</interventions>
          <interventions op="add">Diversionary activity given</interventions>
          <coldReferral op="add">Gambling Service</coldReferral>
          <actions op="add">Lodge Child Protection notification</actions>
          <actions op="add">Transferred to head office/resource fulfilment</actions>
          <warmReferral op="add">Other Service</warmReferral>
        </managementPlan>
      </beyondblue>
    </pluginState>'  -- PLUGIN_STATE_DELTA_XML - xml
            )
    

    Emer

    commented on Dec 12 2012 4:11PM
    Emer
    2219 · 0% · 6
  • Just checked the data. Your query was correct, except for the missing NAMESPACE declaration. Here is a working query that works based on the sample table script you just sent me.

    ;WITH XMLNAMESPACES(
    	DEFAULT 'http://www.medibankhealth.com.au'
    )
    SELECT
    	x.value('guid[1]', 'VARCHAR(32)') as GUID,
    	x.value('interventions[1]', 'VARCHAR(20)') as interventions1,
    	x.value('interventions[2]', 'VARCHAR(20)') as interventions2,
    	x.value('coldReferral[1]', 'VARCHAR(20)') as coldReferral,
    	x.value('warmReferral[1]', 'VARCHAR(20)') as warmReferral
    FROM dbo.EVENT t
    CROSS APPLY PLUGIN_STATE_DELTA_XML.nodes('/pluginState/beyondblue/managementPlan') a(x)
    

    The first 3 lines (;WITH XMLNAMESPACES....) is what you need to add to your query.

    commented on Dec 13 2012 12:18PM
    Jacob Sebastian
    1 · 100% · 32235
  • Morning Jacob,

    That is fantastic thank you very much for all your help.

    Emer

    commented on Dec 13 2012 3:27PM
    Emer
    2219 · 0% · 6
  • Hello, I need some help with an xml column. Please help.

    <order xmlns="http://cp.com">
    	<client id="IP4316000">
    		<quoteback name="requestid">88db1359-fa49</quoteback>
    	</client>
    	<accounting>
    		<pnc>
    			<account>501941TST</account>
    		</pnc>
    	</accounting>
    	<products>
    		<auto_prefill primary_subject="s1" />
    	</products>
    	<dataset>
    		<subjects>
    			<subject id="s1" quoteback="88db1359-fa49-4ff2">
    				<name>
    					<first>MARK</first>
    					<middle />
    					<last>JONES</last>
    				</name>
    				<birthdate>7/14/1970</birthdate>
    				<address ref="a1" type="residence" />
    			</subject>
    		</subjects>
    		<addresses>
    			<address id="a1">
    				<street1>1678 OLD HARRIMAN HWY</street1>
    				<apartment />
    				<city>OLIVER SPRINGS</city>
    				<state>TN</state>
    				<postalcode>37840</postalcode>
    				<zip4 />
    			</address>
    		</addresses>
    	</dataset>
    </order>
    

    I need the following data in columns

    Account First Middle Last birthdate postalcode

    I am trying to write a query to just get the account data but i am not getting any result from it

    what am i doing wrong

    ;WITH XMLNAMESPACES(
    DEFAULT 'http://cp.com')
    
    select accnt.value('account[1]','nvarchar(255)')Account
    from prefillservices.dbo.transactionlog c
    cross apply contents.nodes('/Accounting/pnc/account') orer(accnt)
    where c.configurationvaluecode='TLNRQ'
    
    commented on May 22 2013 12:47PM
    hap1
    2764 · 0% · 4
  • I see that the XPath expression you are using is incorrect. Also, note that XML is case sensitive.

    DECLARE @t TABLE (data XML)
    INSERT INTO @t(data) 
    SELECT '
    <order xmlns="http://cp.com">
    	<client id="IP4316000">
    		<quoteback name="requestid">88db1359-fa49</quoteback>
    	</client>
    	<accounting>
    		<pnc>
    			<account>501941TST</account>
    		</pnc>
    	</accounting>
    	<products>
    		<auto_prefill primary_subject="s1" />
    	</products>
    	<dataset>
    		<subjects>
    			<subject id="s1" quoteback="88db1359-fa49-4ff2">
    				<name>
    					<first>MARK</first>
    					<middle />
    					<last>JONES</last>
    				</name>
    				<birthdate>7/14/1970</birthdate>
    				<address ref="a1" type="residence" />
    			</subject>
    		</subjects>
    		<addresses>
    			<address id="a1">
    				<street1>1678 OLD HARRIMAN HWY</street1>
    				<apartment />
    				<city>OLIVER SPRINGS</city>
    				<state>TN</state>
    				<postalcode>37840</postalcode>
    				<zip4 />
    			</address>
    		</addresses>
    	</dataset>
    </order>'
    
    ;WITH XMLNAMESPACES(
    	DEFAULT 'http://cp.com'
    )
    SELECT accnt.value('.','nvarchar(255)') AS Account
    FROM @t c
    CROSS APPLY data.nodes('order/accounting/pnc/account') orer(accnt)
    
    /*
    result:
    Account
    ----------------------------
    501941TST
    
    */
    
    commented on May 22 2013 1:32PM
    Jacob Sebastian
    1 · 100% · 32235
  • Jacob,

    Why do we use SELECT accnt.value('.','nvarchar(255)') AS Account instead of SELECT accnt.value('account[1]','nvarchar(255)') AS Account Also, since the table is huge and have multiple xml feed with the tags 'order/accounting/pnc/account'. i am getting the result for the query as 501941TST .. 62 times.. how do i get all the values for the tags order/accounting/pnc/account. Please advice. Thanks

    commented on May 22 2013 5:47PM
    hap1
    2764 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 64 – Reading values from an XML column" rated 5 out of 5 by 4 readers
XQuery Lab 64 – Reading values from an XML column , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]