Fetch data from hierarchy in XML.

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

General SQL Server Forums

New to SQL Server Programming

Fetch data from hierarchy in XML.

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2007-10-11 : 09:31:53

My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.

------XML-file---------------------------------------
<my:group1>
<my:group2>
<my:hustypTF>5</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>6</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>7</my:hustypTF>
</my:group2>
</my:group1>
-----------------------------------------------------

------T-SQL for fetching data from XML datatype------
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet
FROM MinaDekl
CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1)
-----------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:58:54

Fixing your sample data to this

DECLARE	@Sample TABLE (FormData XML)

INSERT	@Sample
SELECT	'
<my:group1>
	<my:group2>
		<my:hustypTF>5</my:hustypTF>
	</my:group2>
	<my:group2>
		<my:hustypTF>6</my:hustypTF>
	</my:group2>
	<my:group2>
		<my:hustypTF>7</my:hustypTF>
	</my:group2>
</my:group1>
'

;WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS My)
SELECT		FormData.value('/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'VARCHAR(99)') AS IdFastBet
FROM		@Sample
CROSS APPLY	FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp') AS TempTab(TestTab1)

now renders me the error message
XQuery [@Sample.FormData.nodes()]: ")" was expected.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 16:17:49

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112504

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources