Post reply

Fetching data from xml Column in correct way

  • January 16, 2015 at 2:42 am

    #314565

    I have a table ESRequestInfo in there is a field of XmlData.

    <Record>

    <Header>

    <Table>HRAppEmpObjectivesTRN</Table>

    </Header>

    <Body>

    <Insert>

    <Request>

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

    </Body>

    </Record>

    I have to select data from xmlData field like 49843

    Here is my query

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

    FROM ESRequestInfo AS [R]

    But the query getting data like EmpCatEmpID

    Kindly help me out

  • January 16, 2015 at 3:11 am

    #1770671

    check your xml....the xml supplied is not valid.

  • January 16, 2015 at 3:14 am

    #1770672

    Its a very long xml and its output is also long that I cant post whole xml here.

    That is why I have posted some of it here with the query.

    Can you tell me the way I fetch any column with suitable heading

  • January 16, 2015 at 3:16 am

    #1770674

    If you provide a block of compliant xml i.e. tags which open and close then perhaps I can help...but not as it is. (Maybe someone else is more charitable than I.)

  • January 16, 2015 at 3:20 am

    #1770675

    I have send you whole xml in private message check it out

  • January 16, 2015 at 3:32 am

    #1770677

    'Record/Body/Insert/Request/Column[1]/UpdateInfo/text()' will give you the first occurence of UpdateInfo (Column[1])

    'Record/Body/Insert/Request/Column/UpdateInfo' will give you all results in a fragment.

    <UpdateInfo>TESTPOLICY01</UpdateInfo><UpdateInfo>test Policy 01 for Appraisal</UpdateInfo><UpdateInfo>1</UpdateInfo><UpdateInfo>30</UpdateInfo><UpdateInfo>TOPICA</UpdateInfo><UpdateInfo>Topic A bind with objective</UpdateInfo><UpdateInfo>1</UpdateInfo><UpdateInfo>dd</UpdateInfo><UpdateInfo>01</UpdateInfo><UpdateInfo>Test</UpdateInfo><UpdateInfo>01-Jan-1900</UpdateInfo><UpdateInfo>0</UpdateInfo><UpdateInfo>32</UpdateInfo><UpdateInfo>we</UpdateInfo><UpdateInfo>we</UpdateInfo><UpdateInfo>60</UpdateInfo><UpdateInfo>30</UpdateInfo><UpdateInfo>6/11/2014 12:00:00 AM</UpdateInfo><UpdateInfo>30</UpdateInfo><UpdateInfo>6/11/2014 12:00:00 AM</UpdateInfo>

  • January 16, 2015 at 4:13 am

    #1770685

    What if I have to get all request in seperate rows for each request

Viewing 7 posts - 1 through 6 (of 6 total)

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