Help with For XML Explicit Syntax

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

Transact-SQL (2000)

Help with For XML Explicit Syntax

Author

Topic

dkatz
Starting Member

1 Post

Posted - 2005-09-22 : 16:49:46

can someone help me out. I am trying to fit my data into this template.

<client record_id, system_id, odbid, date_added, date_update>
<first_name>
<last_name></lastname>
<mi_initial></mi_initial>
<soc_sec_no>
<dynamic_data>
<gender></gender>
<race></race>
<dob></dob>
</dynamic_data>
</client>

i am using this selct statement:
select 1 as tag,
null as parent,
record_id as "client!1!record_id",
system_id as "client!1!system_id",
odbid as "client!1!odbid",
date_added as "client!1!date_added",
date_updated as "client!1!date_updated",
null as "client!2!first_name!element",
null as "client!2!last_name!element",
null as "client!3!mi_initial!element",
null as "client!2!soc_sec_no!element",
null as "dynamic_content!3!svpprofgender!element",
null as "dynamic_content!3!svpprofdob!element",
null as "dynamic_content!3!svpprofrace"
from tblrawclients
where MemberRecNo = 24
union all
select 2,1,
record_id, null, null, null, null, first_name, last_name, mi_initial, soc_sec_no,null, null, null
from tblrawclients
where MemberRecNo = 24
union all
select 3,2,
record_id, null, null, null, null, null, null, null, null,gender, dob, race
from tblrawclients
where MemberRecNo = 24
for xml explicit

but i kept getting the error message:
XML tag ID 3 that was originally declared as 'client' is being redeclared as 'dynamic_content'.

how can i edit my statement to get rid of the error message and comply with the template. thank you in advance for any help.

Matthew Bazar
Starting Member

2 Posts

Posted - 2006-01-31 : 13:56:33

dkatz,

I'm having the same error & was wondering if you ever solved it?

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 14:05:57

If you post the DDL for the table, and a couple of INSERT statements to populate it with some data, plus the query then someone here will have a go at solving it for you, I'm sure

Kristen

Matthew Bazar
Starting Member

2 Posts

Posted - 2006-01-31 : 14:43:21

I'm trying to get into this template:

<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>

using the select statement:

SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT

BUT receiving the error:

Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 15:17:50

Mathew: In order to simulate it, and try to find a solution, I/we need a CREATE TABLE statement and a handful of INSERT statements to populate the table with some dummy data.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources