Adding attributes to the root element.

Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
..
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advanceVankayala wrote:

> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/