Hi,
Trying to read following data from an xml column in a table. Please help!
Input table with data and sample query:
USE TEMPDB; GO DECLARE @tempxmlnamespace1 TABLE( id int IDENTITY(1 , 1) , xdata xml); INSERT INTO @tempxmlnamespace1( xdata) VALUES( '<BookSet xmlns:p1="http://schemas.microsoft.com/winfx/2006/xaml" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/workflow" Description="{p1:Null}" Name="DueCharges" ChainingBehavior="Full"> <BookSet.Books> <Book Priority="2147483647" ReevaluationBehavior="Always" Description="{p1:Null}" Active="True" Name="EXPIRY"> <Book.Condition> <BookExpressionCondition Name="{p1:Null}"> <BookExpressionCondition.Expression> <ns0:CodeMethodInvokeExpression xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Contains"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </BookExpressionCondition.Expression> </BookExpressionCondition> </Book.Condition> <Book.ThenActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeAssignStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeAssignStatement.Left> <ns0:CodePropertyReferenceExpression PropertyName="LibraryChargeDeadline"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="get_Item"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeAssignStatement.Left> <ns0:CodeAssignStatement.Right> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeAssignStatement.Right> </ns0:CodeAssignStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ThenActions> <Book.ElseActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeExpressionStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeExpressionStatement.Expression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeFieldReferenceExpression FieldName="MinValue"> <ns0:CodeFieldReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeFieldReferenceExpression.TargetObject> </ns0:CodeFieldReferenceExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="AddDueCharge"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeExpressionStatement.Expression> </ns0:CodeExpressionStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ElseActions> </Book> <Book Priority="2147483646" ReevaluationBehavior="Always" Description="{p1:Null}" Active="True" Name="EXPIRY_1"> <Book.Condition> <BookExpressionCondition Name="{p1:Null}"> <BookExpressionCondition.Expression> <ns0:CodeMethodInvokeExpression xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Contains"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </BookExpressionCondition.Expression> </BookExpressionCondition> </Book.Condition> <Book.ThenActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeAssignStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeAssignStatement.Left> <ns0:CodePropertyReferenceExpression PropertyName="EmployeeChargeDeadline"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="get_Item"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeAssignStatement.Left> <ns0:CodeAssignStatement.Right> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeAssignStatement.Right> </ns0:CodeAssignStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ThenActions> <Book.ElseActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeExpressionStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeExpressionStatement.Expression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeFieldReferenceExpression FieldName="MinValue"> <ns0:CodeFieldReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeFieldReferenceExpression.TargetObject> </ns0:CodeFieldReferenceExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="AddDueCharge"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeExpressionStatement.Expression> </ns0:CodeExpressionStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ElseActions> </Book> <Book Priority="2147483645" ReevaluationBehavior="Always" Description="{p1:Null}" Active="True" Name="EXPIRY_2"> <Book.Condition> <BookExpressionCondition Name="{p1:Null}"> <BookExpressionCondition.Expression> <ns0:CodeMethodInvokeExpression xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Contains"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </BookExpressionCondition.Expression> </BookExpressionCondition> </Book.Condition> <Book.ThenActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeAssignStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeAssignStatement.Left> <ns0:CodePropertyReferenceExpression PropertyName="LibraryCommitmentDeadline"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="get_Item"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeAssignStatement.Left> <ns0:CodeAssignStatement.Right> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeAssignStatement.Right> </ns0:CodeAssignStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ThenActions> <Book.ElseActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeExpressionStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeExpressionStatement.Expression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeFieldReferenceExpression FieldName="MinValue"> <ns0:CodeFieldReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeFieldReferenceExpression.TargetObject> </ns0:CodeFieldReferenceExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="AddDueCharge"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeExpressionStatement.Expression> </ns0:CodeExpressionStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ElseActions> </Book> <Book Priority="2147483644" ReevaluationBehavior="Always" Description="{p1:Null}" Active="True" Name="EXPIRY_3"> <Book.Condition> <BookExpressionCondition Name="{p1:Null}"> <BookExpressionCondition.Expression> <ns0:CodeMethodInvokeExpression xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Contains"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </BookExpressionCondition.Expression> </BookExpressionCondition> </Book.Condition> <Book.ThenActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeAssignStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeAssignStatement.Left> <ns0:CodePropertyReferenceExpression PropertyName="EmployeeCommitmentDeadline"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="get_Item"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueCharges"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeAssignStatement.Left> <ns0:CodeAssignStatement.Right> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeAssignStatement.Right> </ns0:CodeAssignStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ThenActions> <Book.ElseActions> <BookStatementAction> <BookStatementAction.CodeDomStatement> <ns0:CodeExpressionStatement xmlns:ns0="clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" LinePragma="{p1:Null}"> <ns0:CodeExpressionStatement.Expression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">EXPIRY</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">0.0</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression> <ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodePrimitiveExpression> <ns0:CodePrimitiveExpression.Value> <ns1:String xmlns:ns1="clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">06/11/2014 23:59:00</ns1:String> </ns0:CodePrimitiveExpression.Value> </ns0:CodePrimitiveExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="Parse"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> <ns0:CodeFieldReferenceExpression FieldName="MinValue"> <ns0:CodeFieldReferenceExpression.TargetObject> <ns0:CodeTypeReferenceExpression Type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </ns0:CodeFieldReferenceExpression.TargetObject> </ns0:CodeFieldReferenceExpression> </ns0:CodeMethodInvokeExpression.Parameters> <ns0:CodeMethodInvokeExpression.Method> <ns0:CodeMethodReferenceExpression MethodName="AddDueCharge"> <ns0:CodeMethodReferenceExpression.TargetObject> <ns0:CodePropertyReferenceExpression PropertyName="DueChargeProcedureTarget"> <ns0:CodePropertyReferenceExpression.TargetObject> <ns0:CodeThisReferenceExpression /> </ns0:CodePropertyReferenceExpression.TargetObject> </ns0:CodePropertyReferenceExpression> </ns0:CodeMethodReferenceExpression.TargetObject> </ns0:CodeMethodReferenceExpression> </ns0:CodeMethodInvokeExpression.Method> </ns0:CodeMethodInvokeExpression> </ns0:CodeExpressionStatement.Expression> </ns0:CodeExpressionStatement> </BookStatementAction.CodeDomStatement> </BookStatementAction> </Book.ElseActions> </Book> </BookSet.Books> </BookSet>'); WITH XMLNAMESPACES( 'http://schemas.microsoft.com/winfx/2006/xaml' AS p1 , 'http://schemas.microsoft.com/winfx/2006/xaml/workflow' AS p2 , 'clr-namespace:System.CodeDom;Assembly=System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' AS ns0 , 'clr-namespace:System;Assembly=mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' AS ns1) SELECT id , col1.value('./@Name' , 'varchar(1024)')AS Book_Name , col1.value('./@Priority' , 'varchar(1024)')AS Book_Priority --col1.value('(.)' , 'Varchar(1024)') , --col1.query('(.)') FROM @tempxmlnamespace1 AS t OUTER APPLY t.xdata.nodes('/p2:BookSet/p2:BookSet.Books/p2:Book')AS tBook(col1);
Desired Output should look very much similar to following
id Book_Name Book_Priority C1 C2 1 EXPIRY 2147483647 LibraryChargeDeadline EXPIRY 1 EXPIRY 2147483647 LibraryChargeDeadline 06/11/2014 23:59 1 EXPIRY 2147483647 EmployeeChargeDeadline EXPIRY 1 EXPIRY 2147483647 EmployeeChargeDeadline 06/11/2014 23:59 1 EXPIRY_1 2147483646 LibraryChargeDeadline EXPIRY 1 EXPIRY_1 2147483646 LibraryChargeDeadline 06/11/2014 23:59 1 EXPIRY_1 2147483646 EmployeeChargeDeadline EXPIRY 1 EXPIRY_1 2147483646 EmployeeChargeDeadline 06/11/2014 23:59 1 EXPIRY_2 2147483645 LibraryChargeDeadline EXPIRY 1 EXPIRY_2 2147483645 LibraryChargeDeadline 06/11/2014 23:59 1 EXPIRY_2 2147483645 EmployeeChargeDeadline EXPIRY 1 EXPIRY_2 2147483645 EmployeeChargeDeadline 06/11/2014 23:59 1 EXPIRY_3 2147483644 LibraryChargeDeadline EXPIRY 1 EXPIRY_3 2147483644 LibraryChargeDeadline 06/11/2014 23:59 1 EXPIRY_3 2147483644 EmployeeChargeDeadline EXPIRY 1 EXPIRY_3 2147483644 EmployeeChargeDeadline 06/11/2014 23:59
Problem is with this element and related attribute
"ns0:CodePropertyReferenceExpression PropertyName="
Due to its existence of on multiple level in the nested xml, it is returning unwanted data as well.
I have also provided a sample query.
Question is how to read specific attribute "LibraryCommitmentDeadline" as mentioned in C1.
Any question, please let me know.
Thanks.