adding root node in XML explicit query

I have sp called getLookupValue (using for xml explicit) that returns
multiple LookupType Node
<LookupType description="Claim Type">
<LookupValue lkvl_seq="10" lkvl_dsc="Accident" />
<LookupValue lkvl_seq="11" lkvl_dsc="Cancer" />
<LookupValue lkvl_seq="13" lkvl_dsc="Dental" />
</LookupType>
<LookupType description="File Source">
<LookupValue lkvl_seq="7" lkvl_dsc="AFLAC website" />
<LookupValue lkvl_seq="4" lkvl_dsc="Call" />
<LookupValue lkvl_seq="6" lkvl_dsc="Email" />
<LookupValue lkvl_seq="8" lkvl_dsc="Fax" />
<LookupValue lkvl_seq="5" lkvl_dsc="Letter" />
<LookupValue lkvl_seq="9" lkvl_dsc="Walk-in" />
</LookupType>
<LookupType description="File Type">
<LookupValue lkvl_seq="2" lkvl_dsc="CI" />
<LookupValue lkvl_seq="1" lkvl_dsc="EMF" />
<LookupValue lkvl_seq="3" lkvl_dsc="Media" />
</LookupType>
Can someone please let me know how can I modify the stored procedure to add
a root node.
Any sample code would help.
Thanks!
The easiest way to add a root node is to set the root node property on the
command object on the mid-tier.
SQL Server 2005 will have a ROOT() directive.
Best regards
Michael
"Manoj Agarwal" <ManojAgarwal@.discussions.microsoft.com> wrote in message
news:2DA49D31-E8B5-44DF-AF1A-30AC5DB72FC6@.microsoft.com...
>I have sp called getLookupValue (using for xml explicit) that returns
> multiple LookupType Node
>
> <LookupType description="Claim Type">
> <LookupValue lkvl_seq="10" lkvl_dsc="Accident" />
> <LookupValue lkvl_seq="11" lkvl_dsc="Cancer" />
> <LookupValue lkvl_seq="13" lkvl_dsc="Dental" />
> </LookupType>
> <LookupType description="File Source">
> <LookupValue lkvl_seq="7" lkvl_dsc="AFLAC website" />
> <LookupValue lkvl_seq="4" lkvl_dsc="Call" />
> <LookupValue lkvl_seq="6" lkvl_dsc="Email" />
> <LookupValue lkvl_seq="8" lkvl_dsc="Fax" />
> <LookupValue lkvl_seq="5" lkvl_dsc="Letter" />
> <LookupValue lkvl_seq="9" lkvl_dsc="Walk-in" />
> </LookupType>
> <LookupType description="File Type">
> <LookupValue lkvl_seq="2" lkvl_dsc="CI" />
> <LookupValue lkvl_seq="1" lkvl_dsc="EMF" />
> <LookupValue lkvl_seq="3" lkvl_dsc="Media" />
> </LookupType>
>
> Can someone please let me know how can I modify the stored procedure to
> add
> a root node.
> Any sample code would help.
> Thanks!
|||Hi Manoj,
look at this sample..hope this helps..dont worry about the length of the
code..look for what is needed.
output XML :
<products>
<product productID="" LastModifiedDate="">
<replacementPartList>
<replacementPart>
<partNumber></partNumber>
<name><![CDATA[]></name>
<erp></erp>
<revision></revision>
<version></version>
<environment> </environment>
<text><![CDATA[]]></text>
<conftext><![CDATA[]]></conftext>
</replacementPart>
<upgradeInfo><![CDATA[]]></upgradeInfo>
</replacementPartList>
<addOnPartList>
<addOnPart>
<name><![CDATA[]></name>
<erp></erp>
<revision></revision>
<version></version>
<environment> </environment>
<text><![CDATA[]]></text>
<conftext><![CDATA[]]></conftext>
</addOnPart>
<upgradeInfo><![CDATA[]]></upgradeInfo>
</addOnPartList>
</product>
</products>
Query :
SELECT
--product tag --
1 as tag
, NULL as parent
, NULL [products!1]
, NULL [product!2!productID]
, NULL [product!2!LastModifiedDate]
--replacementPart List --
--replacementPart List - add productID for Ordering. it needs to be
hidden. --
, NULL [replacementPartList!3!productID!hide]
--replacement Part--
, NULL [replacementPart!4!partNumber!element]
, NULL [replacementPart!4!name!element]
, NULL [replacementPart!4!erp!element]
, NULL [replacementPart!4!revision!element]
, NULL [replacementPart!4!version!element]
, NULL [replacementPart!4!environment!element]
, NULL [replacementPart!4!text!element]
, NULL [replacementPart!4!conftext!element]
--upgrade info--
, NULL [replacementPartList!3!upgradeInfo!element]
--AddOn Part List--
, NULL [addOnPartList!5!productID!hide]
--AddOn Part--
, NULL [addOnPart!6!partNumber!element]
, NULL [addOnPart!6!name!element]
, NULL [addOnPart!6!erp!element]
, NULL [addOnPart!6!revision!element]
, NULL [addOnPart!6!version!element]
, NULL [addOnPart!6!environment!element]
, NULL [addOnPart!6!text!element]
, NULL [addOnPart!6!conftext!element]
--upgrade info--
, NULL [addOnPartList!5!upgradeInfo!element]
UNION ALL
--Opening replacementPart List tag--
SELECT
--replacementPart List tag--
2 as tag
, 1 as parent
, NULL
, parts.ProductEditorialSKID
, ISNULL(Convert(Varchar(12),Max(parts.LastModifiedD ate),110),'')
--replacementPart List --
, NULL
--replacement Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
--AddOn Part List--
, NULL
--AddOn Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
FROM
#tblInfoWebParts parts
GROUP BY parts.ProductEditorialSKID
UNION ALL
--Opening replacementPart List tag--
SELECT
--replacementPart List tag--
3 as tag
, 2 as parent
, NULL
, parts.ProductEditorialSKID
, NULL
--replacementPart List --
, parts.ProductEditorialSKID
--replacement Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, (SELECT dbo.GetConcatenatedComments(parts.ProductEditorial SKID))
--AddOn Part List--
, NULL
--AddOn Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
FROM
#tblInfoWebParts parts
WHERE
parts.PartType <> 'A'
GROUP BY parts.ProductEditorialSKID
UNION ALL
--replacementPart--
SELECT
--replacementPart tag--
4 as tag
, 3 as parent
, NULL
, parts.ProductEditorialSKID
, NULL
--replacementPart List --
, parts.ProductEditorialSKID
--replacement Part--
, parts.PartNumber
, ISNULL(parts.ItemName,'')
, ISNULL(Convert(Varchar(10),parts.ERP),'')
, ISNULL(parts.RevisionName,'')
, ISNULL(parts.VersionName,'')
, ISNULL(parts.OperatingSystemName,'')
, ''
, ''
--upgrade info--
, NULL
--AddOn Part List--
, NULL
--AddOn Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
FROM
#tblInfoWebParts parts
WHERE
parts.PartType <> 'A' -- indicates that this part is a replacement part or
included part out of the bunch.
UNION ALL
--Add On Part List--
SELECT
--Add On Part List--
5 as tag
, 2 as parent
, NULL
, parts.ProductEditorialSKID
, NULL
--replacementPart List --
, NULL
--replacement Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
--AddOn Part List--
, parts.ProductEditorialSKID
--AddOn Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, (SELECT dbo.GetConcatenatedComments(parts.ProductEditorial SKID))
FROM
#tblInfoWebParts parts
WHERE
parts.PartType = 'A' -- indicates that this part is an add on part out of
the bunch.
GROUP BY parts.ProductEditorialSKID
UNION ALL
--Add On Part--
SELECT
--Add On Part--
6 as tag
, 5 as parent
, NULL
, parts.ProductEditorialSKID
, NULL
--replacementPart List --
, NULL
--replacement Part--
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
--upgrade info--
, NULL
--AddOn Part List--
, parts.ProductEditorialSKID
--AddOn Part--
, ISNULL(parts.PartNumber,'')
, ISNULL(parts.ItemName,'')
, ISNULL(Convert(Varchar(10),parts.ERP),'')
, ISNULL(parts.RevisionName,'')
, ISNULL(parts.VersionName,'')
, ISNULL(parts.OperatingSystemName,'')
, ''
, ''
--upgrade info--
, NULL
FROM
#tblInfoWebParts parts
WHERE
parts.PartType = 'A' -- indicates that this part is an Add On Part out of
the bunch.
ORDER BY [products!1]
, [product!2!productID]
, [replacementPartList!3!productID!hide]
, [replacementPart!4!partNumber!element]
, [addOnPartList!5!productID!hide]
, [addOnPart!6!name!element]
FOR XML EXPLICIT
Av.
http://avdotnet.rediffblogs.com
http://www28.brinkster.com/avdotnet
"Manoj Agarwal" <ManojAgarwal@.discussions.microsoft.com> wrote in message
news:2DA49D31-E8B5-44DF-AF1A-30AC5DB72FC6@.microsoft.com...
> I have sp called getLookupValue (using for xml explicit) that returns
> multiple LookupType Node
>
> <LookupType description="Claim Type">
> <LookupValue lkvl_seq="10" lkvl_dsc="Accident" />
> <LookupValue lkvl_seq="11" lkvl_dsc="Cancer" />
> <LookupValue lkvl_seq="13" lkvl_dsc="Dental" />
> </LookupType>
> <LookupType description="File Source">
> <LookupValue lkvl_seq="7" lkvl_dsc="AFLAC website" />
> <LookupValue lkvl_seq="4" lkvl_dsc="Call" />
> <LookupValue lkvl_seq="6" lkvl_dsc="Email" />
> <LookupValue lkvl_seq="8" lkvl_dsc="Fax" />
> <LookupValue lkvl_seq="5" lkvl_dsc="Letter" />
> <LookupValue lkvl_seq="9" lkvl_dsc="Walk-in" />
> </LookupType>
> <LookupType description="File Type">
> <LookupValue lkvl_seq="2" lkvl_dsc="CI" />
> <LookupValue lkvl_seq="1" lkvl_dsc="EMF" />
> <LookupValue lkvl_seq="3" lkvl_dsc="Media" />
> </LookupType>
>
> Can someone please let me know how can I modify the stored procedure to
add
> a root node.
> Any sample code would help.
> Thanks!

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/

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 advance
Vankayala 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/