stored procedure help - xml explicit |
|
Author |
Message |
bearcatnat
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
>>
>>
>
>
|
|
|
|
|
|
|