|
Home > Archive > MS SQL XML > January 2006 > Viewing XML as recordset.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Viewing XML as recordset.
|
|
| anantatman@gmail.com 2006-01-04, 8:24 pm |
| What's the easiest way to see vertical XML data as a vertical record
set.
e.g. (xml column data)
<DataSet>
<Data id="1"/>Something 1</Data>
<Data id="2"/>Something 2</Data>
<Data id="3"/>Something 3</Data>
</DataSet>
as
1 , Something 1
2 , Something 2
3, Something 3
What's the easiest way to see horizontal XML data as a horizontal
record?
e.g xmlcolumn data.
<DataSet>
<Data id="1">
<CompanyName>Microsoft</BrandName>
<BrandName>Outlook</BrandName>
</Data>
<Data id="2">
<FirstName>Google</LastName>
<SecondName>Gmail</LastName>
</Data>
</DataSet>
as
'
Microsoft, Outlook
Google, Gmail
I am an intermediate to advanced SQL server user. I don't know how to
do some of the complex joins because I usually work with fairly simple
data structures.
I have recently started paying attention to the XML integration into
SQL server and realized
I can do some neat stuff with it. What are some good sources to learn
from examples?
Rahul
| |
| Eugene Kogan [MSFT] 2006-01-10, 3:24 am |
| Look for OPENXML in the documentation. It has lots of examples. Here's one
for you test data:
DECLARE @docHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'
<DataSet>
<Data id="1">Something 1</Data>
<Data id="2">Something 2</Data>
<Data id="3">Something 3</Data>
</DataSet>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocume
nt @docHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/DataSet/Data')
WITH (
id nvarchar(100) '@id',
text nvarchar(100) 'text()')
EXEC sp_xml_removedocumen
t @docHandle
Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.
<anantatman@gmail.com> wrote in message
news:1136423230.115142.148780@z14g2000cwz.googlegroups.com...
> What's the easiest way to see vertical XML data as a vertical record
> set.
>
> e.g. (xml column data)
>
> <DataSet>
> <Data id="1"/>Something 1</Data>
> <Data id="2"/>Something 2</Data>
> <Data id="3"/>Something 3</Data>
> </DataSet>
>
> as
>
> 1 , Something 1
> 2 , Something 2
> 3, Something 3
>
> What's the easiest way to see horizontal XML data as a horizontal
> record?
>
> e.g xmlcolumn data.
>
> <DataSet>
> <Data id="1">
> <CompanyName>Microsoft</BrandName>
> <BrandName>Outlook</BrandName>
> </Data>
> <Data id="2">
> <FirstName>Google</LastName>
> <SecondName>Gmail</LastName>
> </Data>
> </DataSet>
>
> as
> '
> Microsoft, Outlook
> Google, Gmail
>
>
> I am an intermediate to advanced SQL server user. I don't know how to
> do some of the complex joins because I usually work with fairly simple
> data structures.
>
> I have recently started paying attention to the XML integration into
> SQL server and realized
> I can do some neat stuff with it. What are some good sources to learn
> from examples?
>
> Rahul
>
|
|
|
|
|