stored procedure help - xml explicit  
Author Message
bearcatnat





PostPosted: Thu Jul 01 12:54:46 CDT 2004 Top

SQL Server Developer >> stored procedure help - xml explicit

Hi,
I can't seem to get this to work. Its a stored procedure using XML EXPLICIT.

I'd really appreciate any help. Below is the stored procedure and below that
is the xml syntax I'm trying to achieve.

Thank you very much in advance,

Tim (newbie).



CREATE PROCEDURE dbo.FindUser
(

)
AS
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [Users!1],
NULL AS [User!2!userID],
NULL AS [User!2!Username!element],
NULL AS [User!2!Password!element],
NULL AS [User!2!FirstName!element],
NULL AS [User!2!MiddleName!element],
NULL AS [User!2!LastName!element],
NULL AS [User!2!dob],
NULL AS [PostalAddress!3],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL AS [ResidentAddress!3],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL AS [User!2!PhoneNumber!element],
NULL AS [User!2!MobileNumber!element],
NULL AS [User!2!EmailAddress!element]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Users.userID,
Users.username,
Users.password,
Users.firstName,
Users.middleName,
Users.lastName,
Users.dob,
NULL,
NULL AS [PostalAddress!3!Street!element],
NULL AS [PostalAddress!3!Suburb!element],
NULL AS [PostalAddress!3!State!element],
NULL AS [PostalAddress!3!Postcode!element],
NULL AS [PostalAddress!3!Country!element],
NULL,
NULL AS [ResidentAddress!3!Street!element],
NULL AS [ResidentAddress!3!Suburb!element],
NULL AS [ResidentAddress!3!State!element],
NULL AS [ResidentAddress!3!Postcode!element],
NULL AS [ResidentAddress!3!Country!element],
Users.phoneNumber,
Users.mobileNumber,
Users.emailAddress
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Users.postalStreet,
Users.postalSuburb,
Users.postalState,
Users.postalPostcode,
Users.postalCountry,
NULL,
Users.residentStreet,
Users.residentSuburb,
Users.residentState,
Users.residentPostcode,
Users.residentCountry,
NULL,
NULL,
NULL
FROM Users

FOR XML EXPLICIT




<Users>
<User userID="" dob="">
<Username></Username>
<Password></Password>
<FirstName></FirstName>
<MiddleName></MiddleName>
<LastName></LastName>
<PostalAddress>
<Street></Street>
<Suburb></Suburb>
<State></State>
<Postcode></Postcode>
<Country></Country>
</PostalAddress>
<ResidentAddress>
<Street></Street>
<Suburb></Suburb>
<State></State>
<Postcode></Postcode>
<Country></Country>
</ResidentAddress>
<PhoneNumber></PhoneNumber>
<MobileNumber></MobileNumber>
<EmailAddress></EmailAddress>
</User>
<Users>

SQL Server63  
 
 
John





PostPosted: Thu Jul 01 12:54:46 CDT 2004 Top

SQL Server Developer >> stored procedure help - xml explicit Hi

Posting DDL (Create table statements etc.. ), example data (as insert
statements) will help people answer your query. The second query statement
in the union clause does not have a FROM clause which is needed for the
users table.

John



> Hi,
> I can't seem to get this to work. Its a stored procedure using XML
EXPLICIT.
>
> I'd really appreciate any help. Below is the stored procedure and below
that
> is the xml syntax I'm trying to achieve.
>
> Thank you very much in advance,
>
> Tim (newbie).
>
>
>
> CREATE PROCEDURE dbo.FindUser
> (

> )
> AS
> SELECT
> 1 AS Tag,
> NULL AS Parent,
> NULL AS [Users!1],
> NULL AS [User!2!userID],
> NULL AS [User!2!Username!element],
> NULL AS [User!2!Password!element],
> NULL AS [User!2!FirstName!element],
> NULL AS [User!2!MiddleName!element],
> NULL AS [User!2!LastName!element],
> NULL AS [User!2!dob],
> NULL AS [PostalAddress!3],
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL AS [ResidentAddress!3],
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL AS [User!2!PhoneNumber!element],
> NULL AS [User!2!MobileNumber!element],
> NULL AS [User!2!EmailAddress!element]
> UNION ALL
> SELECT
> 2 AS Tag,
> 1 AS Parent,
> NULL,
> Users.userID,
> Users.username,
> Users.password,
> Users.firstName,
> Users.middleName,
> Users.lastName,
> Users.dob,
> NULL,
> NULL AS [PostalAddress!3!Street!element],
> NULL AS [PostalAddress!3!Suburb!element],
> NULL AS [PostalAddress!3!State!element],
> NULL AS [PostalAddress!3!Postcode!element],
> NULL AS [PostalAddress!3!Country!element],
> NULL,
> NULL AS [ResidentAddress!3!Street!element],
> NULL AS [ResidentAddress!3!Suburb!element],
> NULL AS [ResidentAddress!3!State!element],
> NULL AS [ResidentAddress!3!Postcode!element],
> NULL AS [ResidentAddress!3!Country!element],
> Users.phoneNumber,
> Users.mobileNumber,
> Users.emailAddress
> UNION ALL
> SELECT
> 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Users.postalStreet,
> Users.postalSuburb,
> Users.postalState,
> Users.postalPostcode,
> Users.postalCountry,
> NULL,
> Users.residentStreet,
> Users.residentSuburb,
> Users.residentState,
> Users.residentPostcode,
> Users.residentCountry,
> NULL,
> NULL,
> NULL
> FROM Users

> FOR XML EXPLICIT
>
>
>
>
> <Users>
> <User userID="" dob="">
> <Username></Username>
> <Password></Password>
> <FirstName></FirstName>
> <MiddleName></MiddleName>
> <LastName></LastName>
> <PostalAddress>
> <Street></Street>
> <Suburb></Suburb>
> <State></State>
> <Postcode></Postcode>
> <Country></Country>
> </PostalAddress>
> <ResidentAddress>
> <Street></Street>
> <Suburb></Suburb>
> <State></State>
> <Postcode></Postcode>
> <Country></Country>
> </ResidentAddress>
> <PhoneNumber></PhoneNumber>
> <MobileNumber></MobileNumber>
> <EmailAddress></EmailAddress>
> </User>
> <Users>
>
>


 
 
Timothy





PostPosted: Thu Jul 08 08:41:23 CDT 2004 Top

SQL Server Developer >> stored procedure help - xml explicit Hi again,
I got it to work.... well, sort of. When I run it it comes up with the
message: "FOR XML EXPLICIT query contains the invalid column name ''. Use
the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive
integer."

I've pasted it bellow. Thank you once again!

Tim.

CREATE PROCEDURE dbo.FindUser
(

)
AS
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [Users!1],
NULL AS [User!2!userID],
NULL AS [User!2!Username!element],
NULL AS [User!2!Password!element],
NULL AS [User!2!FirstName!element],
NULL AS [User!2!MiddleName!element],
NULL AS [User!2!LastName!element],
NULL AS [User!2!dob],
NULL AS [PostalAddress!3],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL AS [ResidentAddress!3],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL AS [User!2!PhoneNumber!element],
NULL AS [User!2!MobileNumber!element],
NULL AS [User!2!EmailAddress!element]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Users.userID,
Users.username,
Users.password,
Users.firstName,
Users.middleName,
Users.lastName,
Users.dob,
NULL,
NULL AS [PostalAddress!3!Street!element],
NULL AS [PostalAddress!3!Suburb!element],
NULL AS [PostalAddress!3!State!element],
NULL AS [PostalAddress!3!Postcode!element],
NULL AS [PostalAddress!3!Country!element],
NULL,
NULL AS [ResidentAddress!3!Street!element],
NULL AS [ResidentAddress!3!Suburb!element],
NULL AS [ResidentAddress!3!State!element],
NULL AS [ResidentAddress!3!Postcode!element],
NULL AS [ResidentAddress!3!Country!element],
Users.phoneNumber,
Users.mobileNumber,
Users.emailAddress
FROM Users

UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Users.postalStreet,
Users.postalSuburb,
Users.postalState,
Users.postalPostcode,
Users.postalCountry,
NULL,
Users.residentStreet,
Users.residentSuburb,
Users.residentState,
Users.residentPostcode,
Users.residentCountry,
NULL,
NULL,
NULL
FROM Users

FOR XML EXPLICIT


 
 
John





PostPosted: Thu Jul 08 15:14:56 CDT 2004 Top

SQL Server Developer >> stored procedure help - xml explicit Hi

This will work:

CREATE PROCEDURE dbo.FindUser
(

)
AS
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [Users!1],
NULL AS [User!2!userID],
NULL AS [User!2!Username!element],
NULL AS [User!2!Password!element],
NULL AS [User!2!FirstName!element],
NULL AS [User!2!MiddleName!element],
NULL AS [User!2!LastName!element],
NULL AS [User!2!dob],
NULL AS [PostalAddress!3],
NULL AS [PostalAddress!3!Street!element],
NULL AS [PostalAddress!3!Suburb!element],
NULL AS [PostalAddress!3!State!element],
NULL AS [PostalAddress!3!Postcode!element],
NULL AS [PostalAddress!3!Country!element],
NULL AS [ResidentAddress!4],
NULL AS [ResidentAddress!4!Street!element],
NULL AS [ResidentAddress!4!Suburb!element],
NULL AS [ResidentAddress!4!State!element],
NULL AS [ResidentAddress!4!Postcode!element],
NULL AS [ResidentAddress!4!Country!element],
NULL AS [User!2!PhoneNumber!element],
NULL AS [User!2!MobileNumber!element],
NULL AS [User!2!EmailAddress!element]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
Users.userID,
Users.username,
Users.password,
Users.firstName,
Users.middleName,
Users.lastName,
Users.dob,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Users.phoneNumber,
Users.mobileNumber,
Users.emailAddress
FROM Users

UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Users.postalStreet,
Users.postalSuburb,
Users.postalState,
Users.postalPostcode,
Users.postalCountry,
NULL,
Users.residentStreet,
Users.residentSuburb,
Users.residentState,
Users.residentPostcode,
Users.residentCountry,
NULL,
NULL,
NULL
FROM Users

FOR XML EXPLICIT

John



> Hi again,
> I got it to work.... well, sort of. When I run it it comes up with the
> message: "FOR XML EXPLICIT query contains the invalid column name ''. Use
> the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive
> integer."
>
> I've pasted it bellow. Thank you once again!
>
> Tim.
>
> CREATE PROCEDURE dbo.FindUser
> (

> )
> AS
> SELECT
> 1 AS Tag,
> NULL AS Parent,
> NULL AS [Users!1],
> NULL AS [User!2!userID],
> NULL AS [User!2!Username!element],
> NULL AS [User!2!Password!element],
> NULL AS [User!2!FirstName!element],
> NULL AS [User!2!MiddleName!element],
> NULL AS [User!2!LastName!element],
> NULL AS [User!2!dob],
> NULL AS [PostalAddress!3],
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL AS [ResidentAddress!3],
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL AS [User!2!PhoneNumber!element],
> NULL AS [User!2!MobileNumber!element],
> NULL AS [User!2!EmailAddress!element]
> UNION ALL
> SELECT
> 2 AS Tag,
> 1 AS Parent,
> NULL,
> Users.userID,
> Users.username,
> Users.password,
> Users.firstName,
> Users.middleName,
> Users.lastName,
> Users.dob,
> NULL,
> NULL AS [PostalAddress!3!Street!element],
> NULL AS [PostalAddress!3!Suburb!element],
> NULL AS [PostalAddress!3!State!element],
> NULL AS [PostalAddress!3!Postcode!element],
> NULL AS [PostalAddress!3!Country!element],
> NULL,
> NULL AS [ResidentAddress!3!Street!element],
> NULL AS [ResidentAddress!3!Suburb!element],
> NULL AS [ResidentAddress!3!State!element],
> NULL AS [ResidentAddress!3!Postcode!element],
> NULL AS [ResidentAddress!3!Country!element],
> Users.phoneNumber,
> Users.mobileNumber,
> Users.emailAddress
> FROM Users

> UNION ALL
> SELECT
> 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Users.postalStreet,
> Users.postalSuburb,
> Users.postalState,
> Users.postalPostcode,
> Users.postalCountry,
> NULL,
> Users.residentStreet,
> Users.residentSuburb,
> Users.residentState,
> Users.residentPostcode,
> Users.residentCountry,
> NULL,
> NULL,
> NULL
> FROM Users

> FOR XML EXPLICIT
>
>


 
 
Timothy





PostPosted: Fri Jul 09 00:30:42 CDT 2004 Top

SQL Server Developer >> stored procedure help - xml explicit Thank you, i should have worked that out for myself. Stupid me.

Thank you again.




> Hi
>
> This will work:
>
> CREATE PROCEDURE dbo.FindUser
> (

> )
> AS
> SELECT
> 1 AS Tag,
> NULL AS Parent,
> NULL AS [Users!1],
> NULL AS [User!2!userID],
> NULL AS [User!2!Username!element],
> NULL AS [User!2!Password!element],
> NULL AS [User!2!FirstName!element],
> NULL AS [User!2!MiddleName!element],
> NULL AS [User!2!LastName!element],
> NULL AS [User!2!dob],
> NULL AS [PostalAddress!3],
> NULL AS [PostalAddress!3!Street!element],
> NULL AS [PostalAddress!3!Suburb!element],
> NULL AS [PostalAddress!3!State!element],
> NULL AS [PostalAddress!3!Postcode!element],
> NULL AS [PostalAddress!3!Country!element],
> NULL AS [ResidentAddress!4],
> NULL AS [ResidentAddress!4!Street!element],
> NULL AS [ResidentAddress!4!Suburb!element],
> NULL AS [ResidentAddress!4!State!element],
> NULL AS [ResidentAddress!4!Postcode!element],
> NULL AS [ResidentAddress!4!Country!element],
> NULL AS [User!2!PhoneNumber!element],
> NULL AS [User!2!MobileNumber!element],
> NULL AS [User!2!EmailAddress!element]
> UNION ALL
> SELECT
> 2 AS Tag,
> 1 AS Parent,
> NULL,
> Users.userID,
> Users.username,
> Users.password,
> Users.firstName,
> Users.middleName,
> Users.lastName,
> Users.dob,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Users.phoneNumber,
> Users.mobileNumber,
> Users.emailAddress
> FROM Users

> UNION ALL
> SELECT
> 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> Users.postalStreet,
> Users.postalSuburb,
> Users.postalState,
> Users.postalPostcode,
> Users.postalCountry,
> NULL,
> Users.residentStreet,
> Users.residentSuburb,
> Users.residentState,
> Users.residentPostcode,
> Users.residentCountry,
> NULL,
> NULL,
> NULL
> FROM Users

> FOR XML EXPLICIT
>
> John
>


>> Hi again,
>> I got it to work.... well, sort of. When I run it it comes up with the
>> message: "FOR XML EXPLICIT query contains the invalid column name ''. Use
>> the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive
>> integer."
>>
>> I've pasted it bellow. Thank you once again!
>>
>> Tim.
>>
>> CREATE PROCEDURE dbo.FindUser
>> (

>> )
>> AS
>> SELECT
>> 1 AS Tag,
>> NULL AS Parent,
>> NULL AS [Users!1],
>> NULL AS [User!2!userID],
>> NULL AS [User!2!Username!element],
>> NULL AS [User!2!Password!element],
>> NULL AS [User!2!FirstName!element],
>> NULL AS [User!2!MiddleName!element],
>> NULL AS [User!2!LastName!element],
>> NULL AS [User!2!dob],
>> NULL AS [PostalAddress!3],
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL AS [ResidentAddress!3],
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL AS [User!2!PhoneNumber!element],
>> NULL AS [User!2!MobileNumber!element],
>> NULL AS [User!2!EmailAddress!element]
>> UNION ALL
>> SELECT
>> 2 AS Tag,
>> 1 AS Parent,
>> NULL,
>> Users.userID,
>> Users.username,
>> Users.password,
>> Users.firstName,
>> Users.middleName,
>> Users.lastName,
>> Users.dob,
>> NULL,
>> NULL AS [PostalAddress!3!Street!element],
>> NULL AS [PostalAddress!3!Suburb!element],
>> NULL AS [PostalAddress!3!State!element],
>> NULL AS [PostalAddress!3!Postcode!element],
>> NULL AS [PostalAddress!3!Country!element],
>> NULL,
>> NULL AS [ResidentAddress!3!Street!element],
>> NULL AS [ResidentAddress!3!Suburb!element],
>> NULL AS [ResidentAddress!3!State!element],
>> NULL AS [ResidentAddress!3!Postcode!element],
>> NULL AS [ResidentAddress!3!Country!element],
>> Users.phoneNumber,
>> Users.mobileNumber,
>> Users.emailAddress
>> FROM Users

>> UNION ALL
>> SELECT
>> 3 AS Tag,
>> 2 AS Parent,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> NULL,
>> Users.postalStreet,
>> Users.postalSuburb,
>> Users.postalState,
>> Users.postalPostcode,
>> Users.postalCountry,
>> NULL,
>> Users.residentStreet,
>> Users.residentSuburb,
>> Users.residentState,
>> Users.residentPostcode,
>> Users.residentCountry,
>> NULL,
>> NULL,
>> NULL
>> FROM Users

>> FOR XML EXPLICIT
>>
>>
>
>