Applying XML cross apply to get data from XML file



I am applying XML cross apply to extract data from XML project file having multiple nodes by using Stored procedure.

Stored Procedure is as follow`



INSERT INTO UCDetails
SELECT
Usecase.value('@UserID','VARCHAR(100)') AS UCId, --ATTRIBUTE
Usecase.value('@Name','VARCHAR(100)') AS UCName, --ATTRIBUTE
Usecase.value('@PmAuthor','VARCHAR(100)') AS UCActor, --ATTRIBUTE
UCPrecon.value('@Value','VARCHAR(MAX)') AS UCPre, --ATTRIBUTE
UCPostcon.value('@Value','VARCHAR(MAX)') AS UCPost, --ATTRIBUTE


FROM
@xml.nodes('/Project/Models/UseCase')AS TAB(Usecase) OUTER
apply @xml.nodes('/Project/Models/UseCase/TaggedValues/TaggedValueContainer/ModelChildren/TaggedValue[5] ')AS TAB1(UCPrecon)OUTER
apply @xml.nodes('/Project/Models/UseCase/TaggedValues/TaggedValueContainer/ModelChildren/TaggedValue[6]')AS TAB2(UCPostcon)

END


` Instead of getting only single row data I am getting cross multiplication row as shown below


UCId UCName UCActor UCPre UCPost

UC01 Login Bilal Haider User must be Registerd User is Loggined sucessfully


UC01 Login Bilal Haider User must be Registerd User is added


UC01 Login Bilal Haider User is Loggined sucessfully User is Loggined sucessfully


UC01 Login Bilal Haider User is Loggined sucessfully User is added


UC02 Add User Bilal Haider User must be Registerd User is Loggined sucessfully


UC02 Add User Bilal Haider User must be Registerd User is added


UC02 Add User Bilal Haider User is Loggined sucessfully User is Loggined sucessfully


UC02 Add User Bilal Haider User is Loggined sucessfully User is added


Sorry for posting the result like this Right row are highlighted by Bold but Why I am getting all other rows


Please help.


No comments:

Post a Comment