How to select from XML using T-SQL and XQuery

Question

I tried to do the xml parsing with sql.

And I tried to select from the Skill tag but only one row is showing please any one help thanks in advance

My XML format is as below

declare @UserDetails XML
set @UserDetails='
<Registration>
  <objUserBasicData>
    <User_Id>0</User_Id>
    <UserType>0</UserType>
    <UserName>[email protected]</UserName>
    <Password>user123</Password>
    <LastLoginTime>2015-08-08T18:22:53.9953905+05:30</LastLoginTime>
    <IsActive>true</IsActive>
    <CreatedBy>0</CreatedBy>
    <CreatedOn>2015-08-08T18:22:53.9953905+05:30</CreatedOn>
    <ModifiedBy>0</ModifiedBy>
    <ModifiedOn>2015-08-08T18:22:53.9953905+05:30</ModifiedOn>
  </objUserBasicData>
  <objUserDetails>
    <UserDetails_Id>0</UserDetails_Id>
    <User_Id>0</User_Id>
    <FirstName>test</FirstName>
    <MiddleName />
    <LastName>test</LastName>
    <ContactName />
    <Email>[email protected]</Email>
    <AlternativeEmail />
    <RegisterDate>2015-08-08T18:22:54.0164133+05:30</RegisterDate>
    <Address1 />
    <Address2 />
    <Country>0</Country>
    <State>0</State>
    <City>1</City>
    <PostCode />
    <HomePhone />
    <Mobile>1231231232</Mobile>
    <Fax />
    <Marketing>false</Marketing>
    <HasCV>false</HasCV>
    <CVName>564195d7-4dfa-42e0-abc1-ef611be588a2.doc</CVName>
    <HasJobsByEmail>false</HasJobsByEmail>
    <Website />
    <CreatedBy>0</CreatedBy>
    <CreatedOn>2015-08-08T18:22:54.0164133+05:30</CreatedOn>
    <ModifiedBy>0</ModifiedBy>
    <ModifiedOn>2015-08-08T18:22:54.0164133+05:30</ModifiedOn>
    <DateOfBirth>2015-08-10T00:00:00</DateOfBirth>
    <CurrentJobTitle>test</CurrentJobTitle>
    <Career_Id>2</Career_Id>
    <PrefferedLocation>1</PrefferedLocation>
    <ExperienceInYears>5</ExperienceInYears>
    <Gender>1</Gender>
  </objUserDetails>
  <Skills>
    <int>2</int>
    <int>1</int>
  </Skills>
</Registration>'

I used the query to select the data is

SELECT
    TEMPTABLE.UserData.value('int[1]','int') AS int
FROM
    @UserDetails.nodes('/Registration/Skills')AS TEMPTABLE(UserData)

it gets result as

int
-----------
2

the 1 is missing

The I used

    DECLARE @XMLDocPointer INT    
    EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @UserDetails    


   SELECT *
   FROM OPENXML(@XMLDocPointer,'/Registration/Skills',3)    
   WITH  ([int] INT)     

   EXEC sp_xml_removedocument @XMLDocPointer

Again the result is the same

int
-----------
2

what do i have to do get the result as

int
-----------
2
1

har07 · Accepted Answer · 2015-08-08 14:03:07Z

3

That's because you only select the first int element by saying int[1]. Try this way instead :

SELECT TEMPTABLE.UserData.value('.','int') AS int
FROM
    @UserDetails.nodes('/Registration/Skills/int')AS TEMPTABLE(UserData)

Sqlfiddle Demo

output :

| int |
|-----|
|   2 |
|   1 |

answered Aug 8, 2015 at 14:03

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sreerejith S S Over a year ago

thank you very much It worked I tried

SELECT TEMPTABLE.UserData.value('int[1]','int') AS int  FROM @UserDetails.nodes('/Registration/Skills/int')AS TEMPTABLE(UserData)

but its was getting null .Again thank you

Collectives™ on Stack Overflow

How to select from XML using T-SQL and XQuery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related