Camillia
asked on
How can I find this data?
This is SQL 2016 Developer.
I have this XML. You see there are these tags:
Main
Chapter
Section
num
heading
note
I can get the "chapter" and 'section' info. But I want to get the "num" and "heading" and "note" withing the 'section' tag. How can I do this?
I have this XML. You see there are these tags:
Main
Chapter
Section
num
heading
note
I can get the "chapter" and 'section' info. But I want to get the "num" and "heading" and "note" withing the 'section' tag. How can I do this?
declare @xml XML
set @xml = '
<main>
<chapter style="-uslm-lc:I81" id="ida3144ad1-0381-11e7-859c-c7e65b0ded31" identifier="/us/usc/t8/ch1"><num value="1">CHAPTER 1—</num><heading>GENERAL PROVISIONS</heading>
<section status="repealed" identifier="/us/usc/t8/s1...18"><num value="1 to 18">§§ 1 to 18.</num><heading> Repealed or Omitted</heading><notes type="uscNote" id="ida3144ad3-0381-11e7-859c-c7e65b0ded31">
<note topic="removalDescription" id="ida3144ad4-0381-11e7-859c-c7e65b0ded31">
<p style="-uslm-lc:I21" class="indent0">These sections, relating to citizenship, were affected by the Act of 1940, former section 501 et seq. of this title.</p>
</note>
</notes>
</section>
</chapter>
</main>'
--select @xml
create table #temp
(
xmlCol XML
)
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',
'' as SectionHeading, --***** here, how can I get "Repealed or Omitted" text that belongs to this tag --> <heading> Repealed or Omitted</heading>
isnull( n.c.value('section[1]','varchar(max)'),'') AS 'section', --gets all notes in each section
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.
ASKER
Thanks. I was just looking into that. This helps a lot.
Open in new window