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

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?

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)  

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
for others, try

		n.c.value('section[1]/num[1]','varchar(max)') AS SectionNum,
		n.c.value('section[1]/heading[1]','varchar(max)') AS SectionHeading,
		n.c.value('section[1]/notes[1]/note[1]/p[1]','varchar(max)') AS SectionNote,

Open in new window

Avatar of Camillia

ASKER

Thanks. I was just looking into that. This helps a lot.