Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Change this SQL to get all nodes

This is SQL Server 2016 Developer edition

I have this example. I know why I only get the first nodes data. It's because I use section[1]/heading[1]. How can I get the rest? I think I can do section[2]/heading[2] to get the next one but there has to be another way....

declare @xml XML

set @xml = '
<main>

  <chapter style="-uslm-lc:I81" id="ida3144ae7-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/ch3">
    <num value="3">CHAPTER 3—</num>
    <heading>CIVIL RIGHTS</heading>
    <section status="transferred" style="-uslm-lc:I80" id="ida3144ae8-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/s41...43">
      <num value="41 to 43">§§?41 to 43.</num>
      <heading> Transferred</heading>
      <notes type="uscNote" id="ida3144ae9-0381-11e7-859c-c7e65b0ded31">
        <note style="-uslm-lc:I76" topic="codification" id="ida3144aea-0381-11e7-859c-c7e65b0ded31">
          <heading class="centered smallCaps">Codification</heading>
          <p style="-uslm-lc:I21" class="indent0">Sections 41 to 43 transferred to sections 1981 to 1983, respectively, of Title 42, The Public Health and Welfare.</p>
        </note>
      </notes>
    </section>
    <section status="repealed" style="-uslm-lc:I80" id="ida3144aeb-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/s44 /us/usc/t8/s45">
      <num value="44, 45">§§?44, 45.</num>
      <heading> Repealed. <ref href="/us/act/1948-06-25/ch645">June 25, 1948, ch. 645</ref>, §?21, <ref href="/us/stat/62/862">62 Stat. 862</ref>, eff. <date date="1948-09-01">Sept. 1, 1948</date></heading>
      <notes type="uscNote" id="ida3144aec-0381-11e7-859c-c7e65b0ded31">
        <note topic="removalDescription" id="ida3144aed-0381-11e7-859c-c7e65b0ded31">
          <p style="-uslm-lc:I21" class="indent0">Section 44, <ref href="/us/act/1875-03-01/ch114">act Mar. 1, 1875, ch. 114</ref>, §?4, <ref href="/us/stat/18/336">18 Stat. 336</ref>, related to exclusion of jurors on account of race or color. See <ref href="/us/usc/t18/s243">section 243 of Title 18</ref>, Crimes and Criminal Procedure.</p>
        </note>
        <note topic="removalDescription" id="ida3144aee-0381-11e7-859c-c7e65b0ded31">
          <p style="-uslm-lc:I21" class="indent0">Section 45, acts <ref href="/us/act/1875-03-01/ch114">Mar. 1, 1875, ch. 114</ref>, §?3, <ref href="/us/stat/18/336">18 Stat. 336</ref>; <ref href="/us/act/1896-05-28/ch252">May 28, 1896, ch. 252</ref>, §?19, <ref href="/us/stat/29/184">29 Stat. 184</ref>, related to prosecutions for banning jurors because of race or color. See <ref href="/us/usc/t18/s243">section 243 of Title 18</ref>.</p>
        </note>
      </notes>
    </section>
    <section status="transferred" style="-uslm-lc:I80" id="ida3144aef-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/s46...51">
      <num value="46 to 51">§§?46 to 51.</num>
      <heading> Transferred</heading>
      <notes type="uscNote" id="ida3144af0-0381-11e7-859c-c7e65b0ded31">
        <note style="-uslm-lc:I76" topic="codification" id="ida3144af1-0381-11e7-859c-c7e65b0ded31">
          <heading class="centered smallCaps">Codification</heading>
          <p style="-uslm-lc:I21" class="indent0">Sections 46 to 51 transferred to sections 1984 to 1987, 1989, and 1990, respectively, of Title 42, The Public Health and Welfare.</p>
        </note>
      </notes>
    </section>
    <section status="repealed" style="-uslm-lc:I80" id="ida3144af2-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/s52">
      <num value="52">§?52.</num>
      <heading> Repealed. <ref href="/us/act/1948-06-25/ch646">June 25, 1948, ch. 646</ref>, §?39, <ref href="/us/stat/62/992">62 Stat. 992</ref>, eff. <date date="1948-09-01">Sept. 1, 1948</date></heading>
      <notes type="uscNote" id="ida31694e3-0381-11e7-859c-c7e65b0ded31">
        <note topic="removalDescription" id="ida31694e4-0381-11e7-859c-c7e65b0ded31">
          <p style="-uslm-lc:I21" class="indent0">Section, R.S. §?1986; acts <ref href="/us/act/1896-05-28/ch252">May 28, 1896, ch. 252</ref>, §?6, <ref href="/us/stat/29/179">29 Stat. 179</ref>; <ref href="/us/act/1919-02-26/ch49">Feb. 26, 1919, ch. 49</ref>, §?1, <ref href="/us/stat/40/1182">40 Stat. 1182</ref>; <ref href="/us/act/1921-02-11/ch46">Feb. 11, 1921, ch. 46</ref>, <ref href="/us/stat/41/1099">41 Stat. 1099</ref>, related to fees of district attorneys, marshals, and clerks of court.</p>
        </note>
      </notes>
    </section>
    <section status="transferred" style="-uslm-lc:I80" id="ida31694e5-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/s53...56">
      <num value="53 to 56">§§?53 to 56.</num>
      <heading> Transferred</heading>
      <notes type="uscNote" id="ida31694e6-0381-11e7-859c-c7e65b0ded31">
        <note style="-uslm-lc:I76" topic="codification" id="ida31694e7-0381-11e7-859c-c7e65b0ded31">
          <heading class="centered smallCaps">Codification</heading>
          <p style="-uslm-lc:I21" class="indent0">Sections 53 to 56 transferred to sections 1991, 1992, former section 1993, and section 1994, respectively, of Title 42, The Public Health and Welfare.</p>
        </note>
      </notes>
    </section>
  </chapter>
</main>'

--select @xml

create table #temp
(
  xmlCol XML
)

--drop table #temp

insert into #temp
select @xml

SELECT  n.c.value('heading[1]','varchar(max)') AS 'heading' ,  
        n.c.value('num[1]','varchar(max)') AS 'chapter number',
		isnull(n.c.value('section[1]/num[1]','varchar(max)'),'') AS 'Section Number',
		isnull(n.c.value('section[1]/heading[1]','varchar(max)'),'') AS 'Section Heading',
		--isnull(n.c.value('toc[1]/layout[1]/header[2]','varchar(max)'),'') AS 'Section Header2',
		--isnull(n.c.value('toc[1]/layout[1]/header[3]','varchar(max)'),'') AS 'Section Header3',
		--isnull(n.c.value('toc[1]/layout[1]/header[4]','varchar(max)'),'') AS 'Section Header4',
		--isnull(n.c.value('toc[1]/layout[1]/header[5]','varchar(max)'),'') AS 'Section Header5',
		--isnull(n.c.value('toc[1]/layout[1]/header[6]','varchar(max)'),'') AS 'Section Header6',
		--isnull(n.c.value('toc[1]/layout[1]/header[7]','varchar(max)'),'') AS 'Section Header7',
		--isnull(n.c.value('section[1]/notes[1]/note[1]/heading[2]','varchar(max)'),'') AS NoteHeading,
		--isnull(n.c.value('section[1]/notes[1]/note[1]/p[1]','varchar(max)'),'') AS SectionNote,
	    substring(isnull(n.c.value('section[1]/notes[1]/note[1]/.','varchar(max)'),''), len(n.c.value('section[1]/notes[1]/note[1]/heading[1]','varchar(max)'))+1,5000) SectionNote,
		isnull(n.c.value('subchapter[1]','varchar(max)'),'') AS 'subchapter',
		n.c.value('.','varchar(max)') AS 'all per chapter'
FROM    #temp t
Cross   Apply XmlCol.nodes('/main/chapter') n(c)  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Camillia

ASKER

Thanks, guys. I think they should both work. Let me play around with it and compare to the site that displays the data. I'll open  a new question if I have other issues.