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?

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/

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" <mahotrash@yahoo.de> wrote in message
news:uIFktwFMJHA.1160@TK2MSFTNGP05.phx.gbl...
> 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/

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/

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" <mahotrash@yahoo.de> wrote in message
news:O7zpO4GMJHA.2164@TK2MSFTNGP02.phx.gbl...
> 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/