SQL xml - getting an error when using ../

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 2005 Forums

Transact-SQL (2005)

SQL xml - getting an error when using ../

Author

Topic

bobmad
Starting Member

1 Post

Posted - 2008-04-28 : 13:13:13

I am writing a stored procedure which takes a single parameter, a typed xml document. My procedure parses the xml and inserts rows into a table.

Here is the error message that I am getting:
TSD4001: XQuery [value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element(InternalErrorId,xs:anyType) ?'.

The following code is a sample of what I am doing in my sp:

Declare @xml xml

Set @xml = '
<Requests>
<Request RequestNo="100">
<Error>
<ErrorCode>4000</ErrorCode>
</Error>
</Request>
<Request RequestNo="200">
<Error>
<ErrorCode>9999</ErrorCode>
</Error>
</Request>
</Requests>
'

Select
t.c.value('ErrorCode[1]', 'varchar(5)'),
t.c.value('../@RequestNo', 'int')
From @xml.nodes('/Requests/Request/Error') t(c)

This code fragment runs just fine and returns what I would expect. However, as soon as I change the code and add an insert statement which inserts the result set into a table I get the error above?

Insert Into myTable(
ErrorCode,
RequestNo
)
Select
t.c.value('ErrorCode[1]', 'varchar(5)'),
t.c.value('../@RequestNo', 'int')
From @xml.nodes('/Requests/Request/Error') t(c)

What am I missing?

Thanks

Starnamer
Starting Member

4 Posts

Posted - 2008-04-30 : 04:49:29

From what you've given I'd say your table definition was off.

I copied most of your message text into SSMS (from the 'Declare' to the end of the 'Insert') and replaced the sentence starting 'This code fragment runs...' by the SQL:


drop table myTable
create table myTable(ErrorCode varchar(5), RequestNo int)

The code, including the INSERT, then runs without error. Hence the conclusion that either your table definition is wrong or your example doesn't properly reflect your real code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources