Cannot be bound error

Question

Cannot be bound error

brendan.tate

SSC Journeyman

Points: 80
More actions
June 11, 2012 at 7:52 am

#100030

Hi All,
Im trying to create a FOR XML statement to export each row individually in my database into an individual XML file.
Everytime i run the query i get the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.payloads.ContentReference" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "dbo.payloads.Id" could not be bound.
My code is below:
SELECT (Select dbo.payloads.ContentReference as ImportFileName
FOR
XML PATH ('FileInfo'),
TYPE
),
(Select 'PS-SCR/1' as ParentReference,
dbo.payloads.Id as UniqueID,
'' as Folder,
'False' as RegisterAsRecord,
'OLMMailMerge' as DocumentType
FOR
XML PATH ('Release'),
TYPE
)
FOR
XML PATH (''),
ROOT('XmlMetadataProcess')
GO
Any ideas?
Regards
Brendan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Neither ContentReference nor ID are defined column aliases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

brendan.tate SSC Journeyman Points: 80 More actions · Answer 2

Do i need to?

Dbo.Payloads is the table?

Sorry im new to this!

Regards

Brendan

Lowell SSC Guru Points: 323518 More actions · Answer 3

you cannot refernece a column without saying what table it comes from...looks like you need "FROM dbo.payloads"

ie:

...Select dbo.payloads.ContentReference as ImportFileName
FROM dbo.payloads
FOR
XML PATH ('FileInfo'),
TYPE...

maybe like this:

SELECT (Select dbo.payloads.ContentReference as ImportFileName

FROM dbo.payloads

FOR

XML PATH ('FileInfo'),

TYPE

),

(Select 'PS-SCR/1' as ParentReference,

dbo.payloads.Id as UniqueID,

'' as Folder,

'False' as RegisterAsRecord,

'OLMMailMerge' as DocumentType

FROM dbo.payloads

FOR

XML PATH ('Release'),

TYPE

)

FOR

XML PATH (''),

ROOT('XmlMetadataProcess')

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brendan.tate SSC Journeyman Points: 80 More actions · Answer 4

Fantastic thats worked, your a star! Pardon my ignorance!

The only thing thats happening is that its creating a giant XML file with all the rows in, what i want it to do is create an XML file for each row.

How do i modify my query for that to happen?

Thanks in advance, its much appreciated

Regards

Brendan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

brendan.tate (6/11/2012)
Do i need to?

Sure, otherwise how does SQL know what you mean

Dbo.Payloads is the table?

Maybe, but you haven't got any FROM clause, so again, how does SQL know what you mean?

The format for a SELECT is

SELECT <columns>

FROM <table>

The only time you can omit the FROM is when you're selecting constants, like SELECT getdate() as CurrentDate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass