Repeatable xml elements in xml columns

That looks good so far. What I will end up needing to do eventually is
taking some section sets along with some other xml nodes and combining them
into a view to create a column view of the document. Here is a simple
example:

Take the sections I gave earlier and add some stuff to it like this:

<Document>
<Sections>
<Section ID=1 Title=Section 1">
<Text>
This is section 1. Put your own text here.
</Text>
<Section ID=2 Title="Section 2">
<Text>
This is section 2. Put your own text here.
</Text>
</Section>
<!--Put as many sections here as needed. The actual number of sections
veriess from document to document -->
</Sections>
<Event Title="Sample event" Date="2008-10-17T07:00:00Z" Duration="01:00:00">
<Location Name="Somewhere" Address="112 StreetName" City="ACity" State="MI"
ZipCode="44321">
</Event>
</Document>

How would you turn this into a view where each row represents this entire
document?


"Martin Honnen" wrote in message

> Andy B wrote:
>> If I have a document fragment that looks like this:
>>
>> <Section ID= 1 Title="Section 1">
>> <Text>
>> This is section 1. Put your own text here.
>> </Text>
>> </Section>
>> <Section ID=2 Title="Section 2">
>> <Text>
>> This is section 2. Put your own text here.
>> </Text>
>> </Section>
>>
>> And the count of the Section elements are not known (1 xml column could
>> have 5 section elements and the other one could have 10), how do you
>> access these types of "repeating elements" with xpath? The other
>> interesting question would be how you turn this type of xml column into a
>> view?
>
> Well if you want to select all Section elements with XQuery/XPath then
> Section
> or
> /Section
> does that, it selects all those Section elements.
> You could pass that to the XQuery nodes method as follows:
>
> DECLARE @x XML;
> SET @x = N'<Section ID="1" Title="Section 1">
> <Text>
> This is section 1. Put your own text here.
> </Text>
> </Section>
> <Section ID="2" Title="Section 2">
> <Text>
> This is section 2. Put your own text here.
> </Text>
> </Section>';
>
> SELECT
> X.S.value('@ID', 'int') AS ID,
> X.S.value('@Title', 'nvarchar(20)') AS Title,
> X.S.value('Text[1]', 'nvarchar(200)') AS Text
> FROM
> @x.nodes('Section') AS X(S);
>
> Result is
>
> 1 Section 1 This is section 1. Put your own text here.
> 2 Section 2 This is section 2. Put your own text here.
>
>
> Or what problem do you exactly have to find an XPath expresssion?
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
 >> Stay informed about: Repeatable xml elements in xml columns 

Andy B wrote:

> Take the sections I gave earlier and add some stuff to it like this:
>
> <Document>
> <Sections>
> <Section ID=1 Title=Section 1">
> <Text>
> This is section 1. Put your own text here.
> </Text>
> <Section ID=2 Title="Section 2">
> <Text>
> This is section 2. Put your own text here.
> </Text>
> </Section>
> <!--Put as many sections here as needed. The actual number of sections
> veriess from document to document -->
> </Sections>
> <Event Title="Sample event" Date="2008-10-17T07:00:00Z" Duration="01:00:00">
> <Location Name="Somewhere" Address="112 StreetName" City="ACity" State="MI"
> ZipCode="44321">
> </Event>
> </Document>
>
> How would you turn this into a view where each row represents this entire
> document?

Sorry, I am not able to see the problem. If you want to create a view
you could just do
CREATE VIEW viewName
AS SELECT xmlColumnName FROM tableName;
for instance. That looks too simple to be what you are looking for but I
am currently not able to understand where you have problems creating a
view from a table with a column of type xml as for a view it does not
matter which type a column has.



--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
 >> Stay informed about: Repeatable xml elements in xml columns 

This might sound really interesting, but Out of the second sample xml
document I posted (the one with the Event node) I need to create a view from
it. Here is an example I can give you so you can understand what I mean:

This view was created from an xml column in the base table:

create view [News].[NewsArticlesView]
as

SELECT ID, Article.value('/NewsArticle/@Title[1]', 'VARCHAR(100)') AS
Title, Article.value('/NewsArticle/@Date[1]', 'DateTime') AS Date,
Article.value('NewsArticle/Description[1]',
'VARCHAR(200)') AS Description, Article.value('NewsArticle/Body[1]',
'VARCHAR(max)') AS Body, LastModifiedFROM [News].[News];

My question and worry is about the Sections node. I need to know how to get
the section nodes in the Sections node to show up in the view in a single
row with the rest of the xml document.
"Martin Honnen" wrote in message

> Andy B wrote:
>
>> Take the sections I gave earlier and add some stuff to it like this:
>>
>> <Document>
>> <Sections>
>> <Section ID=1 Title=Section 1">
>> <Text>
>> This is section 1. Put your own text here.
>> </Text>
>> <Section ID=2 Title="Section 2">
>> <Text>
>> This is section 2. Put your own text here.
>> </Text>
>> </Section>
>> <!--Put as many sections here as needed. The actual number of sections
>> veriess from document to document -->
>> </Sections>
>> <Event Title="Sample event" Date="2008-10-17T07:00:00Z"
>> Duration="01:00:00">
>> <Location Name="Somewhere" Address="112 StreetName" City="ACity"
>> State="MI" ZipCode="44321">
>> </Event>
>> </Document>
>>
>> How would you turn this into a view where each row represents this entire
>> document?
>
> Sorry, I am not able to see the problem. If you want to create a view you
> could just do
> CREATE VIEW viewName
> AS SELECT xmlColumnName FROM tableName;
> for instance. That looks too simple to be what you are looking for but I
> am currently not able to understand where you have problems creating a
> view from a table with a column of type xml as for a view it does not
> matter which type a column has.
>
>
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
 >> Stay informed about: Repeatable xml elements in xml columns