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!