July 27, 2010 at 8:01 am
Hi
I have the below xml in a field that is read from a record. How would i constuct a single SELECT (in 2005)to extract the contents of <Value> of 83.11 from the value node below "<Caption>SUBFORMULA</Caption>" ?
'<ProcessTraceNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Caption>Rounding</Caption>
<Description>Nearest to 2 dec</Description>
<Value>227.62</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>PeriodValue</Caption>
<Description />
<Value>227.62029921097232876712328767</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>Formula</Caption>
<Description>holdings etc</Description>
<Value>83081.409212004900000000000</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>HOLDING</Caption>
<Description>UHHOLDING * prc</Description>
<Value>8308140.921200490000000000000</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>*</Caption>
<Description>Multiply</Description>
<Value>8308140.921200490000000000000</Value>
<FunctionId>12</FunctionId>
<FunctionSymbol>*</FunctionSymbol>
<Children>
<ProcessTraceNode>
<Caption>UHHOLD</Caption>
<Description>Unit Holder Holding</Description>
<Value>14888.557100000000</Value>
<FunctionId>200201</FunctionId>
<FunctionSymbol>UHHOLDING</FunctionSymbol>
<Children />
</ProcessTraceNode>
<ProcessTraceNode>
<Caption>LINKED</Caption>
<Description>Linked f</Description>
<Value>558.021900000000</Value>
<FunctionId>200200</FunctionId>
<FunctionSymbol>LINKED</FunctionSymbol>
<Children />
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
<ProcessTraceNode>
<Caption>Net </Caption>
<Description>PRODUCTSCALE</Description>
<Value>83081.409212004900000000000</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>PRODUCTSCALE</Caption>
<Description>Fee Scale </Description>
<Value>83081.409212004900000000000</Value>
<FunctionId>200202</FunctionId>
<FunctionSymbol>PRODUCTSCALE</FunctionSymbol>
<Children>
<ProcessTraceNode>
<Caption>Source Value</Caption>
<Description />
<Value>8308140.921200490000000000000</Value>
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children>
<ProcessTraceNode>
<Caption>SUBFORMULA</Caption>
<Description>LIFE_</Description>
<Value>83.11</Value>
<FunctionId>3</FunctionId>
<FunctionSymbol>SUBFORMULA</FunctionSymbol>
<Children />
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
<ProcessTraceNode>
<Caption>Formula Period</Caption>
<Description>Per Annum </Description>
<Value />
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children />
</ProcessTraceNode>
<ProcessTraceNode>
<Caption>Calculation Cycle</Caption>
<Description>Daily</Description>
<Value />
<FunctionId>0</FunctionId>
<FunctionSymbol />
<Children />
</ProcessTraceNode>
</Children>
</ProcessTraceNode>
</Children>
</ProcessTraceNode>'
to start off with, i guess the code would be something like:
SELECT
b.header.value('Value[1]', 'varchar(20)') AS Typex2
FROM @bb.nodes('//ProcessTraceNode') AS b(header)
July 27, 2010 at 5:47 pm
You were close.
All that's missing is a WHERE clause:
SELECT
b.header.value('Value[1]', 'varchar(20)') AS Typex2
FROM @bb.nodes('//ProcessTraceNode') AS b(header)
WHERE b.header.value('Caption[1]', 'varchar(20)') ='SUBFORMULA'
July 28, 2010 at 1:27 am
Thanks Lutz !
July 28, 2010 at 4:39 pm
You're welcome 😀
That's what you get when providing ready to use sample data, expected result and your current approach: a verified solution matching your requirement 😉
So, you did a great job!!
July 29, 2010 at 1:40 am
nice to hear.
do you by any chance know of some documentation on the various functions of sql Xml fields and how they are used.
for example , why (header) is used and what are the various methods of @bb such as header.value
couldnt i just use b.value
thanks again.
Ronald
(SELECT
b.header.value('Value[1]', 'varchar(30)')
FROM
@FeesXML.nodes('//ProcessTraceNode') AS b(header)
WHERE b.header.value('Caption[1]', 'varchar(20)') ='SUBFORMULA')
July 29, 2010 at 3:03 am
ron5 (7/29/2010)
nice to hear.do you by any chance know of some documentation on the various functions of sql Xml fields and how they are used.
for example , why (header) is used and what are the various methods of @bb such as header.value
couldnt i just use b.value
thanks again.
Ronald
(SELECTb.header.value('Value[1]', 'varchar(30)')
FROM
@FeesXML.nodes('//ProcessTraceNode') AS b(header)
WHERE b.header.value('Caption[1]', 'varchar(20)') ='SUBFORMULA')
I know of two good resources:
1) to understand the basic concept read through the XQuery section in BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server).
2) For a wide list of sample codes I usually refer to Jacob Sebastians blog
Regarding your specific example:
In your query above you have @FeesXML as the XML variable. Applying the .nodes method against this xml file will internally shred the xml file in a "pseudo-table-like" structure (I guess there are people around being able to describe the internal structure much better then I can...). The table will be referenced as "b" and the various elements as a column named "header" since you used AS b(header). Sometimes I use T(c) instead which is for Table(column).
The benefit: in your SELECT statement you can reference the various XML structures by using SELECT Table.Column.Method(), or, in your case: SELECT b.header.value()
August 11, 2010 at 9:33 am
You can also filter in your XPath expression. For example:
SELECT
b.header.value('Value[1]', 'varchar(20)') AS Typex2
FROM @bb.nodes('//ProcessTraceNode[contains(FunctionSymbol[1],"SUBFORMULA")]') AS b(header)
I'm not sure which is more efficient.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply