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.

You are right. I just corrected it.

Thank for catching this.

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>

Unfortunately, I am not familiar with the environment you mentioned. My interaction with XML is mostly in and around SQL server.

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

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
*/

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.

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.

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

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.

Morning Jacob,

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

Emer

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'

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

*/

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