Post reply

I Have To Select Specific Column from xml column

  • January 16, 2015 at 12:56 am

    #397880

    I have data in xml form in table ESRequestInfo in field xmldata in this form.

    <Record>

    <Header>

    <Table>HRAppEmpObjectivesTRN</Table>

    <PKeys>

    <key>AppPolicyCode</key>

    <key>EmpCat</key>

    <key>EmpID</key>

    <key>AppTopicCode</key>

    <key>AppEmpObjNo</key>

    </PKeys>

    </Header>

    <Body>

    <Insert>

    <Request>

    <Column>

    <ColumnName>AppPolicyCode</ColumnName>

    <Caption>Policy Code</Caption>

    <UpdateInfo>AFY-14</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppPolicyDesc</ColumnName>

    <Caption>Policy Description</Caption>

    <UpdateInfo>Final Year Appraisal 2014</UpdateInfo>

    <CurrentInfo />

    <Behavior>FO</Behavior>

    <DataType />

    </Column>

    <Column>

    <ColumnName>EmpCat</ColumnName>

    <Caption>Employee Category</Caption>

    <UpdateInfo>1</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>char</DataType>

    </Column>

    <Column>

    <ColumnName>EmpID</ColumnName>

    <Caption>Employee ID</Caption>

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppTopicCode</ColumnName>

    <Caption>Topic Code</Caption>

    <UpdateInfo>T001</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppTopicDesc</ColumnName>

    <Caption>Topic Description</Caption>

    <UpdateInfo>Smart Goals</UpdateInfo>

    <CurrentInfo />

    <Behavior>FO</Behavior>

    <DataType />

    </Column>

    <Column>

    <ColumnName>AppEmpObjNo</ColumnName>

    <Caption>Objective No</Caption>

    <UpdateInfo>1</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjDesc</ColumnName>

    <Caption>Objective Description</Caption>

    <UpdateInfo>request was rejected now entering the correct one and saving</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>text</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjTargetDate</ColumnName>

    <Caption>Target Date</Caption>

    <UpdateInfo>01-Jan-1900</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjTargetValue</ColumnName>

    <Caption>Target Value</Caption>

    <UpdateInfo>0</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjMarks</ColumnName>

    <Caption>Weightage</Caption>

    <UpdateInfo>50</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjComments</ColumnName>

    <Caption>Comments</Caption>

    <UpdateInfo>no comments, tab indexing improper</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjMeasureIndicator</ColumnName>

    <Caption>Measure Indicator</Caption>

    <UpdateInfo>no measure indicator</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppFormTypeWeightage</ColumnName>

    <Caption>Form Type Weightage</Caption>

    <UpdateInfo>70</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>CreatedBy</ColumnName>

    <Caption />

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>CreatedOn</ColumnName>

    <Caption />

    <UpdateInfo>1/16/2015 12:00:00 AM</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    <Column>

    <ColumnName>LastUser</ColumnName>

    <Caption />

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>LastUpdate</ColumnName>

    <Caption />

    <UpdateInfo>1/16/2015 12:00:00 AM</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    </Request>

    <Request>

    <Column>

    <ColumnName>AppPolicyCode</ColumnName>

    <Caption>Policy Code</Caption>

    <UpdateInfo>AFY-14</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppPolicyDesc</ColumnName>

    <Caption>Policy Description</Caption>

    <UpdateInfo>Final Year Appraisal 2014</UpdateInfo>

    <CurrentInfo />

    <Behavior>FO</Behavior>

    <DataType />

    </Column>

    <Column>

    <ColumnName>EmpCat</ColumnName>

    <Caption>Employee Category</Caption>

    <UpdateInfo>1</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>char</DataType>

    </Column>

    <Column>

    <ColumnName>EmpID</ColumnName>

    <Caption>Employee ID</Caption>

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppTopicCode</ColumnName>

    <Caption>Topic Code</Caption>

    <UpdateInfo>T001</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppTopicDesc</ColumnName>

    <Caption>Topic Description</Caption>

    <UpdateInfo>Smart Goals</UpdateInfo>

    <CurrentInfo />

    <Behavior>FO</Behavior>

    <DataType />

    </Column>

    <Column>

    <ColumnName>AppEmpObjNo</ColumnName>

    <Caption>Objective No</Caption>

    <UpdateInfo>2</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjDesc</ColumnName>

    <Caption>Objective Description</Caption>

    <UpdateInfo>On editing objectives system considering the edited one, hence does not allow to even rating is not exceeded from 100</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>text</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjTargetDate</ColumnName>

    <Caption>Target Date</Caption>

    <UpdateInfo>01-Jan-1900</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjTargetValue</ColumnName>

    <Caption>Target Value</Caption>

    <UpdateInfo>0</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjMarks</ColumnName>

    <Caption>Weightage</Caption>

    <UpdateInfo>50</UpdateInfo>

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjComments</ColumnName>

    <Caption>Comments</Caption>

    <UpdateInfo />

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppEmpObjMeasureIndicator</ColumnName>

    <Caption>Measure Indicator</Caption>

    <UpdateInfo />

    <CurrentInfo />

    <Behavior>FQ</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>AppFormTypeWeightage</ColumnName>

    <Caption>Form Type Weightage</Caption>

    <UpdateInfo>70</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>numeric</DataType>

    </Column>

    <Column>

    <ColumnName>CreatedBy</ColumnName>

    <Caption />

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>CreatedOn</ColumnName>

    <Caption />

    <UpdateInfo>1/16/2015 12:00:00 AM</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    <Column>

    <ColumnName>LastUser</ColumnName>

    <Caption />

    <UpdateInfo>49843</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>varchar</DataType>

    </Column>

    <Column>

    <ColumnName>LastUpdate</ColumnName>

    <Caption />

    <UpdateInfo>1/16/2015 12:00:00 AM</UpdateInfo>

    <CurrentInfo />

    <Behavior>QO</Behavior>

    <DataType>datetime</DataType>

    </Column>

    </Request>

    </Insert>

    <Update />

    <Delete />

    </Body>

    </Record>

    From this Xml I have to select specific ColumnName, UpdateInfo and Caption like EmpID, Employee ID and 49843.

    Kindly help me. Thank you so much in advance for helping me

  • January 16, 2015 at 12:59 am

    #1770656

    I Have made this query

    SELECT

    CAST((R.[XMLData].query('/Record/Body/Insert/Request/Column/ColumnName/text()')) as varchar(8000)) as TableInfo

    FROM ESRequestInfo AS [R]

    But it is getting the result like this....

    AppPolicyDescEmpCatEmpIDEmpNameAppTopicCodeAppTopicDescAppEmpObjNoAppEmpObjDescAppEmpObjTargetDateAppEmpObjTargetValueAppEmpObjMarksAppEmpObjCommentsAppEmpObjMeasureIndicatorAppFormTypeWeightageCreatedByCreatedOnLastUserLastUpdateAppPolicyDescEmpCatEmpIDEmpNameAppTopicCodeAppTopicDescAppEmpObjNoAppEmpObjDescAppEmpObjTargetDateAppEmpObjTargetValueAppEmpObjMarksAppEmpObjCommentsAppEmpObjMeasureIndicatorAppFormTypeWeightageCreatedByCreatedOnLastUserLastUpdateAppPolicyDescEmpCatEmpIDEmpNameAppTopicCodeAppTopicDescAppEmpObjNoAppEmpObjDescAppEmpObjTargetDateAppEmpObjTargetValueAppEmpObjMarksAppEmpObjCommentsAppEmpObjMeasureIndicatorAppFormTypeWeightageCreatedByCreatedOnLastUserLastUpdate

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply