I am trying to export data from an emailing system in the format of XML. how do i get the attachment part of my xml output from my SQL code to be self containing and not contain user data inside the attachment XMl node?\[code\]SELECT Message.Subject AS Subject, Message.Body AS Body, Message.crDate AS MessageCreationDate, MessageDetail.IsRead AS isRead, MessageDetail.ReadDate AS ReadDate, -- attachment data AttachmentDetail.Name AS AttachmentName, AttachmentDetail.Size AS AttachmentSize, [User].id AS SMC_USER_ID, [User].UFID AS OnlineClientIdentifier, [User].UserType AS UserType, [User].Segment AS Online_User_Segment, Recipient.RecipientTypeId, UPPER(RecipientType.Type) AS RecipientType, REPLACE(Recipient.EmailAddress, '#', [User].Id) AS EmailAddress, Recipient.FriendlyName FROM [Message] WITH (NOLOCK) LEFT OUTER JOIN MessageDetail ON MessageDetail.MessageId = Message.id LEFT OUTER JOIN [User] ON [User].Id = MessageDetail.UserId LEFT OUTER JOIN MessageRecipient Recipient ON Recipient.MessageId = Message.id LEFT OUTER JOIN MessageRecipient mr2 on mr2.MessageId = Recipient.MessageId LEFT OUTER JOIN RecipientType ON Recipient.RecipientTypeId = RecipientType.Id LEFT OUTER JOIN MessageAttachment ON MessageAttachment.MessageId = Message.id LEFT OUTER JOIN AttachmentDetail ON AttachmentDetail.AttachmentId = MessageAttachment.AttachmentId FOR XML AUTO, ELEMENTS , root('data') \[/code\]Here is the xml result that i want to have adjusted \[code\]<data> <Message> <MessageId>105</MessageId> <Subject>FW: new test image</Subject> <Body>hi world</Body> <MessageCreationDate>2012-06-21T07:14:29.783</MessageCreationDate> <MessageDetail> <isRead>1</isRead> <ReadDate>2012-06-21T07:15:15.950</ReadDate> <AttachmentDetail> <AttachmentName>image002.jpg</AttachmentName> <AttachmentSize>17759</AttachmentSize> <AttachmentMIMEType>image/jpeg</AttachmentMIMEType> <User> <SMC_USER_ID>100005</SMC_USER_ID> <OnlineClientIdentifier>UserTest</OnlineClientIdentifier> <UserType></UserType> <Online_User_Segment>default</Online_User_Segment> <Recipient> <RecipientTypeId>1</RecipientTypeId> <RecipientType>FROM</RecipientType> <EmailAddress>[email protected]</EmailAddress> <FriendlyName>Iyer, Srividya</FriendlyName> </Recipient> <Recipient> <RecipientTypeId>1</RecipientTypeId> <RecipientType>FROM</RecipientType> <EmailAddress>[email protected]</EmailAddress> <FriendlyName>Iyer, Srividya</FriendlyName> </Recipient> <Recipient> <RecipientTypeId>2</RecipientTypeId> <RecipientType>TO</RecipientType> <EmailAddress>[email protected]</EmailAddress> <FriendlyName>GREGORY AMOROSO</FriendlyName> </Recipient> <Recipient> <RecipientTypeId>2</RecipientTypeId> <RecipientType>TO</RecipientType> <EmailAddress>[email protected]</EmailAddress> <FriendlyName>GREGORY AMOROSO</FriendlyName> </Recipient> </User> </AttachmentDetail> </MessageDetail> </Message></data>\[/code\]I don't want \[code\]<AttachmentDetail>\[/code\] to include data outside the actual attachment data. How can I get the \[code\]</AttachmentDetail>\[/code\] (closing tag) right after the attachment data itself?
selecting data as XML from sql server
- Thread starter archchula
- Start date