Create XML from SQL Server 2005 data using FOR XML
I'm trying to create an Excel XML that I want to store in an XML Field in SQL Server 2005. I have gotten this far:
WITH XMLNAMESPACES (
'urn:schemas-microsoft-com:office:spreadsheet' as "s",
'urn:schemas-microsoft-com:office:office' as "o",
'urn:schemas-microsoft-com:office:excel' as "x"
)
select 'Order' as "@s:Name",
(
select
'String' as 's:Cell/s:Data/@s:Type',
[Order] as 's:Cell/s:Data',
null as 'tmp',
'String' as 's:Cell/s:Data/@s:Type',
[Material] as 's:Cell/s:Data',
null as 'tmp',
'String' as 's:Cell/s:Data/@s:Type',
[Ship-To] as 's:Cell/s:Data'
from
(
select
'Order' as [Order],
'Material' as [Material],
'Ship-To' as [Ship-To]
union all
select
[Order],
[Material],
[Ship-To]
from Orders
WHERE [Material] IN(1234,5678))
) as Temp
FOR XML PATH('s:Row'), type
) AS 's:Table'
FOR XML PATH('s:Worksheet'), root('s:Workbook')
Here's my output:
<s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com开发者_开发百科:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
<s:Worksheet s:Name="Order">
<s:Table>
<s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
<s:Cell>
<s:Data s:Type="String">Order</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">Material</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">Ship-To</s:Data>
</s:Cell>
</s:Row>
<s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
<s:Cell>
<s:Data s:Type="String">200909</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">1234</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">US</s:Data>
</s:Cell>
</s:Row>
<s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
<s:Cell>
<s:Data s:Type="String">200909</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">5678</s:Data>
</s:Cell>
<s:Cell>
<s:Data s:Type="String">ASIA</s:Data>
</s:Cell>
</s:Row>
</s:Table>
</s:Worksheet>
</s:Workbook>
What I want is to eliminate the namespace in the <s:Row>
node. I want to get rid of this: xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet"
from <s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
Anybody have an idea how to do this?
Each FOR XML clause will add a namespace declaration. The only way I know to get rid of them is to build the entire document in one single 'for xml' query, and that is not feasible.
精彩评论