Re: Retrieving and Combining XML

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In SQL Server 2005, you can use ROOT('myRoot') in the FOR XML clause.

In SQL Server 2000, your workaround works. Alternatively, there is a root
property on your connection that you can set in ADO, OLEDB, ADO.Net to get
the root element added on the client.

Best regards
Michael

"gilly3" <news@xxxxxxxxxxxxxxxx> wrote in message
news:Xns96FCA5F95596AnewsNOSPAMgilly3com@xxxxxxxxxxxxxxxx
> "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in
> news:uZgb5Zq2FHA.3880@xxxxxxxxxxxxxxxxxxxx:
>
>> FOR XML EXPLICIT was a good try. But you will need the !xml directive
> in
>> your column alias.
>>
>> Eg,
>>
>> select .... , xmlData as "element!1!row!xml" .... FOR XML EXPLICIT.
>>
>> Best regards
>> Michael
>
> Thanks, that fixes my formatting problem, but I still had trouble
> getting each record under a common root node.
>
> My sql looked like this:
>
> select
> 1 tag,
> null parent,
> [xmlData] [xRoot!1!xElement!xml]
> from xmlTable
> for xml explicit
>
> this gave each record two parent nodes like this with no common root
> node:
>
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
>
> I want one parent node, and for that node to be the root of all the
> records. I managed to make it work by adding a parent node in my
> select, and eliminating extra nodes by using !xmltext, instead of !xml
> like this:
>
>
> select
> 1 tag,
> null parent,
> null [xRoot!1!!xmltext],
> null [foo!2!!xmltext]
> union all
> select 2,
> 1,
> null,
> [xmlData]
> from xmlTable
> for xml explicit
>
> This works, but it seems like a bit of a hack. Is there a more elegant
> solution? If not, I'll just be happy this works as well as it does.
>
> thanks
>
> -ivan.


.

Relevant Pages

  • Re: for auto question
    ... Note that FOR XML in SQL Server 2005 is adding a ROOT directive. ... > property of the ADO command object, the RootTag property of the ADO.NET ...
    (microsoft.public.sqlserver.xml)
  • Re: SQL to XML to XSL
    ... Download the sample code for the last three chapters. ... there on how to do an XSLT transform directly to the HTTP stream using XML ... SQL Server does not add a <root> element if you do not ...
    (microsoft.public.dotnet.xml)
  • Re: xml element name
    ... See if this helps (assumes SQL Server 2005) ... declare @x xml ...
    (microsoft.public.sqlserver.xml)
  • Re: Download the JAVA , .NET and SQL Server interview with answers
    ... 2000 Interview questions of .NET, JAVA and SQL Server Interview ... XML Integration ...
    (comp.lang.java.programmer)
  • Re: Download the JAVA , .NET and SQL Server interview PDF
    ... 2000 Interview questions of .NET, JAVA and SQL Server Interview ... XML Integration ...
    (comp.lang.java.programmer)