Prev: Varbinary - Word Documents
Next: (Advance Programming) Parameter Returned as Value
From: DWalker07 on 12 Mar 2010 15:01
I have been reading the FOR XML documentation, and looking at samples on
the 'Net. I am trying to produce a string of XML, for testing, that looks
like this (slightly edited the values), but I can't quite get there.

Declare @IdList XML
Set @IdList = '<ArrayOfString>
<string>C4DEB399-C9AF-DE11-B9B2-009999999999</string>
<string>C5DEB399-C9AF-DE11-B9B2-009999999999</string>
<string>C6DEB399-C9AF-DE11-B9B2-009999999999</string>
<string>C7DEB399-C9AF-DE11-B9B2-009999999999</string>
</ArrayOfString>'

I want to select a single UniqueIdentifier column from a table and have the
output look like the XML string above. Something along the lines of:

Select <UniqueIdentifierColumn>
From <table>
Where <condition>
For XML <something something>

Any help will be appreciated.

David Walker
From: Plamen Ratchev on 12 Mar 2010 20:09
Try this:

CREATE TABLE Foo (
foo_guid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

INSERT INTO Foo VALUES(NEWID());
INSERT INTO Foo VALUES(NEWID());

SELECT foo_guid AS string
FROM Foo
FOR XML PATH(''), ROOT('ArrayOfString');

/*

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------
<ArrayOfString><string>10BBD5AB-25B4-4B28-8CD3-DE1D4DDDB32C</string>
<string>7BF0B2C3-7640-4E32-9DBA-E0C2618A7A1C</string></ArrayOfString>

*/

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Varbinary - Word Documents
Next: (Advance Programming) Parameter Returned as Value