not returning the root level value

Mike 21 Reputation points
2022-02-10T12:50:47.05+00:00

I have the following code in SQL which is return the correct values on the FromDate and OrgName. the problem is in the indvlPK is returning NULL. Thanks

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(4000)  
SET @XmlDocument = 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>'  
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument  
SELECT *  FROM OPENXML (@XmlDocumentHandle, '/ROOT/Indvl/EmpHss/EmpHs')  
WITH (indvlPK  varchar(50) '/ROOT/Indvl/Info/@indvlPK',  
      FromDate      varchar(50) '@fromDt',  
      OrgName         varchar(50) '@orgNm')  
EXEC sp_xml_removedocument @XmlDocumentHandle

Please can you advise on how to return Also the indvlPK?

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,605 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,911 Reputation points
    2022-02-10T21:35:09.413+00:00

    Hi @Mike ,

    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         |  
    +---------+----------+-----------------------------+  
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful