not returning the root level value

Accepted answer

Yitzhak Khabinsky 25,116

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases.

It is strongly recommended to re-write your SQL and switch it to XQuery.

Please try the following solution.

SQL

 DECLARE @XmlDocument XML =   
 N'<ROOT>  
 <Indvl>  
 <Info lastNm="Dobbins" firstNm="William" midNm="Makay" indvlPK="7147798" actvAGReg="Y" link="https://adviserinfo.sec.gov/individual/summary/7147798"/>  
 <OthrNms>  
 <OthrNm lastNm="Dobbins" firstNm="Makay"/>  
 <OthrNm lastNm="Dobbins" firstNm="William"/>  
 </OthrNms>  
 <CrntEmps>  
 <CrntEmp orgNm="CUSO FINANCIAL SERVICES, L.P." orgPK="42132" str1="10150 MEANLEY DRIVE, 1ST FLOOR" city="SAN DIEGO" state="CA" cntry="United States" postlCd="92131">  
 <CrntRgstns>  
 <CrntRgstn regAuth="UT" regCat="RA" st="APPROVED" stDt="2020-01-30"/>  
 </CrntRgstns>  
 <BrnchOfLocs>  
 <BrnchOfLoc str1="2722 E Red Cliff Drive" city="St George" state="UT" cntry="United States" postlCd="84790"/>  
 <BrnchOfLoc str1="590 West Mesquite Blvd" city="Mesquite" state="NV" cntry="United States" postlCd="89024"/>  
 <BrnchOfLoc str1="1564 West Sunset Blvd" city="St George" state="UT" cntry="United States" postlCd="84770"/>  
 </BrnchOfLocs>  
 </CrntEmp>  
 </CrntEmps>  
 <Exms>  
 <Exm exmCd="S66" exmNm="Uniform Combined State Law Examination" exmDt="2019-10-24"/>  
 </Exms>  
 <Dsgntns/>  
 <PrevRgstns/>  
 <EmpHss>  
 <EmpHs fromDt="06/2019" orgNm="CUSO Financial Services, LP" city="San Diego" state="CA"/>  
 <EmpHs fromDt="01/2019" toDt="06/2019" orgNm="Umemployed" city="St George" state="UT"/>  
 <EmpHs fromDt="09/2018" toDt="01/2019" orgNm="Soltis Investment Advisors" city="St George" state="UT"/>  
 <EmpHs fromDt="12/2016" toDt="09/2018" orgNm="America First Credit Union" city="St George" state="UT"/>  
 <EmpHs fromDt="03/2015" toDt="12/2016" orgNm="America First Credit Union" city="St George" state="UT"/>  
 <EmpHs fromDt="08/2014" toDt="03/2015" orgNm="Under Armour" city="Lehi" state="UT"/>  
 <EmpHs fromDt="03/2014" toDt="08/2014" orgNm="California Pizza Kitchen" city="Orem" state="UT"/>  
 <EmpHs fromDt="06/2009" toDt="03/2014" orgNm="Full-time Education" city="Herriman" state="UT"/>  
 </EmpHss>  
 <OthrBuss/>  
 <DRPs/>  
 </Indvl>  
</ROOT>';  
  
 SELECT h.value('(Info/@indvlPK)[1]', 'INT' ) AS indvlPK  
	, c.value('@fromDt', 'varchar(50)') AS FromDate  
	, c.value('@orgNm', 'varchar(50)') AS OrgName  
 FROM @XmlDocument.nodes('/ROOT/Indvl') AS t(h)  
	CROSS APPLY t.h.nodes('EmpHss/EmpHs') AS t1(c);

Output

+---------+----------+-----------------------------+  
| indvlPK | FromDate |           OrgName           |  
+---------+----------+-----------------------------+  
| 7147798 | 06/2019  | CUSO Financial Services, LP |  
| 7147798 | 01/2019  | Umemployed                  |  
| 7147798 | 09/2018  | Soltis Investment Advisors  |  
| 7147798 | 12/2016  | America First Credit Union  |  
| 7147798 | 03/2015  | America First Credit Union  |  
| 7147798 | 08/2014  | Under Armour                |  
| 7147798 | 03/2014  | California Pizza Kitchen    |  
| 7147798 | 06/2009  | Full-time Education         |  
+---------+----------+-----------------------------+

Mike 21 Reputation points

2022-02-11T04:54:08.463+00:00

Dear @Yitzhak Khabinsky Thank you so much for your reply the query worked well the only problem is that if i have several indvlPKs it is onlu returning a single indvlPK of the XML.

Thanks in advance
Viorel 112.7K Reputation points

2022-02-11T06:27:00.9+00:00

Your original script seems to work too. If you have several <Indvl> elements, then you can try replacing '/ROOT/Indvl/Info/@indvlPK' with '../../Info/@indvlPK'. However, there are alternatives that do not use '../'.

Show your new XML if this does not work.
Yitzhak Khabinsky 25,116 Reputation points

2022-02-11T13:41:55.103+00:00

@Mike ,

Your sample XML has one single indvlPK.
As it was mentioned earlier, you need to provide a minimal reproducible example: ##1-4.

I updated the answer. Check it out.
Mike 21 Reputation points

2022-02-11T17:36:37.147+00:00
Dear @Viorel , I'm also trying to get the CrntRgstn Node Values (<CrntRgstn regAuth="UT" regCat="RA" st="APPROVED" stDt="2020-01-30"/>) with the orgPK at the CrntEmp Level and the indvlPK at the Info Level like the following

indvlPK orgPK regAuth regCat st stDt 7147798 42132 UT RA APPROVED 2020-01-30

.Any advise on how to accomplish such scenario?

Thanks

not returning the root level value

0 additional answers