Hi. As I understand, you have created the table ADMIN_Grant_Exporter_Files_XML with 74 rows, one for each <row> element in RePORTER_PRJ_X_FY2017_007.xml. Each row contains a single xml field called XMLData. You would like T-SQL procedures for queries listed below. I can provide these procedures.
1. Search for all records by ORG_DUNS number, and show APPLICATION_ID and matched ORG_DUNS.
2. Search for all records by ORG_DUNS number and absolute TERM, and show APPLICATION_ID, matched ORG_DUNS, PI/PI_NAME, PI/PID_ID, and matched TERM.
3. Search for all records by ORG_DUNS number and portion of TERM, and show APPLICATION_ID, matched ORG_DUNS, PI/PI_NAME, PI/PI_ID, and matched TERM.
4. Search for all records by ORG_DUNS number and part of PI name, and show APPLICATION_ID, matched ORG_DUNS, and the PI_NAME and PI_ID of the matched PI.
Q. What should happen when a row contains multiple PI's?
a. show first PI name and id.
b. show first two PIs name and id.
c. create separate rows for each PI.
Q. What should happen when a row contains multiple matching terms?
a. show first matching term.
b. show first two matching terms.
c. create separate rows for each matching term.
My opening bid assumes the simplest case, (a), in both questions.
Have I understood correctly?