Camillia
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....
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER