XML query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML query help

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-07-07 : 03:39:37

I have written xml query .. but I need output using single query without using union..

I want to fetch questions from outernodes and as well as inner nodes..
Please see below sql written using UNION...
How can i use to fetch question from outer node and inner node (RepeatRegion)..



CREATE TABLE #trmp (xmlcol XML)
INSERT INTO #trmp VALUES ('<QuestionSet>
  <Group id="grp_1" name="General">
    <Question id="ProductCode" type="shortans" required="true" requiredtext="You must enter a Product Code" cols="30">
    </Question>
    <Question id="Name" type="shortans" required="true" requiredtext="You must enter a Product Name">
    </Question>
    <Question id="Content" type="html" cols="30">
    </Question>
  </Group>
  <Group id="grp_2" name="Attributes">
    <Question id="location" type="mcss" basecontrol="dropdown" AllowNull="False" NULLNAME=":::Select:::">
    </Question>
    <Question id="MaxWattage" type="shortans">
    </Question>
    <Question id="MinWattage" type="shortans">
    </Question>
    <Question id="Voltage" type="shortans">
    </Question>
    <Question id="Amperage" type="shortans">
    </Question>
    <Question id="FittingColour" type="mcss" basecontrol="dropdown">
    </Question>
    <Question id="BeamAngle" type="shortans">
    </Question>
    <Question id="Cap" type="mcss" basecontrol="dropdown">
    </Question>
    <Question id="CutOutRound" type="shortans">
    </Question>
    <Question id="CutOutSquareWidth" type="shortans">
    </Question>
    <Question id="CutOutSquareHeight" type="shortans">
    </Question>
    <Question id="Dimmable" type="mcss" basecontrol="radio" layout="horizontal">
    </Question>
    <Question id="IPRating" type="shortans">
    </Question>
    <Question id="Lumens" type="shortans">
    </Question>
    <RepeatRegion RepeatButtonText="Add" limit="30">
      <Question id="ProductImage">
      </Question>
    </RepeatRegion>
  </Group>
</QuestionSet>')

SELECT a.value('(@id)[1]', 'varchar(50)') QuestionID  FROM #trmp 
	CROSS APPLY xmlcol.nodes('QuestionSet/Group/Question') AS X(a) 
UNION
SELECT  a.value('(@id)[1]', 'varchar(50)') FROM #trmp  
  CROSS APPLY xmlcol.nodes('QuestionSet/Group/RepeatRegion/Question') AS X(a)

DROP TABLE #trmp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-07 : 04:02:50

[code]SELECT n.value('(@id)[1]', 'VARCHAR(50)')
FROM #trmp
CROSS APPLY xmlcol.nodes('(/QuestionSet/Group//Question)') AS x(n)[/code]

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-07-07 : 04:27:27

Hi,

Great Thanks : )
Can you please explain me about written line xml path??


"//"
CROSS APPLY	xmlcol.nodes('(/QuestionSet/Group//Question)') AS x(n)

quote:
Originally posted by SwePeso

SELECT n.value('(@id)[1]', 'VARCHAR(50)')
FROM #trmp
CROSS APPLY xmlcol.nodes('(/QuestionSet/Group//Question)') AS x(n)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-07 : 04:44:42

It is very much like the "*" in DOS, a wildcard type of thing.

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-07-07 : 05:16:55

ok..
Thank you : )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources