MS SQL XML - XML Count Attributes

Author

XML Count Attributes

jbrozek@gmail.com

2006-01-19, 8:24 pm

I have a few questions about xml in SQL 2005.

1) I have some xml in an xml field. This is it's shape (variable)

<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep> -- single row
<rep x1="z1" x2="z2" x3="z3" x4="z4">some value</rep> -- single row

What I'm looking for is how to count the number of attributes
(x1-xInfinite)

So if I query the above, 4 would be returned.

2) In addtion to that, if it is possible, I'm looking to get those
names out dynamically.

So if I queried above, I would get the following info out (it doesn't
have to be a comma, just any string)

x1,x2,x3,x4

3) The ultimate solution would be something that would return this

x1 x2 x3 x4
y1 y2 y3 y4
z1 z2 z3 z4

but I don't think their is a graceful way to do this.

Thank you so much for the help!
JB

Peter Flynn

2006-01-20, 8:24 pm

jbrozek@gmail.com wrote:
> I have a few questions about xml in SQL 2005.
>
> 1) I have some xml in an xml field. This is it's shape (variable)
>
> <rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep> -- single row
> <rep x1="z1" x2="z2" x3="z3" x4="z4">some value</rep> -- single row
>
> What I'm looking for is how to count the number of attributes
> (x1-xInfinite)
>
> So if I query the above, 4 would be returned.

<xsl:value-of select="count(@*)"/>

> 2) In addtion to that, if it is possible, I'm looking to get those
> names out dynamically.
>
> So if I queried above, I would get the following info out (it doesn't
> have to be a comma, just any string)
>
> x1,x2,x3,x4

<xsl:template match="rep">
<xsl:for-each select="@*">
<xsl:value-of select="name()"/>
<xsl:if test="position()!=last()">
<xsl:text>,</xsl:text>
</xsl:if>
</xsl:for-each>
</xsl:template>

> 3) The ultimate solution would be something that would return this
>
> x1 x2 x3 x4
> y1 y2 y3 y4
> z1 z2 z3 z4
>
> but I don't think their is a graceful way to do this.

Assuming your XML file is:

<?xml version="1.0" ?>
<root>
<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep>
<rep x1="z1" x2="z2" x3="z3" x4="z4">some value</rep>
</root>

the following XSLT does the table:

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">

<xsl:output method="html" indent="yes"/>

<xsl:template match="/">
<table>
<tr>
<xsl:for-each select="root/rep[1]/@*">
<th>
<xsl:value-of select="name()"/>
</th>
</xsl:for-each>
</tr>
<xsl:apply-templates/>
</table>
</xsl:template>

<xsl:template match="rep">
<tr>
<xsl:for-each select="@*">
<td>
<xsl:value-of select="."/>
</td>
</xsl:for-each>
</tr>
</xsl:template>

</xsl:stylesheet>

giving:

<table>
<tr>
<th>x1</th>
<th>x2</th>
<th>x3</th>
<th>x4</th>
</tr>

<tr>
<td>y1</td>
<td>y2</td>
<td>y3</td>
<td>y4</td>
</tr>

<tr>
<td>z1</td>
<td>z2</td>
<td>z3</td>
<td>z4</td>
</tr>

</table>

Adapt as required.

///Peter
--
XML FAQ: http://xml.silmaril.ie/

Kent Tegels

2006-01-20, 8:24 pm

Hello jbrozek@gmail.com,

> I have a few questions about xml in SQL 2005.
> 1) I have some xml in an xml field. This is it's shape (variable)
> <rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep> -- single row
> <rep x1="z1" x2="z2" x3="z3" x4="z4">some value</rep> -- single row
> What I'm looking for is how to count the number of attributes
> (x1-xInfinite)
> So if I query the above, 4 would be returned.

declare @x xml
set @x = '<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep><rep x1="z1"
x2="z2" x3="z3" x4="z4">some value</rep>'
select 'question 1',t.c. value('count(@*)','i
nt')
from @x.nodes('/rep') as t(c)

> 2) In addtion to that, if it is possible, I'm looking to get those
> names out dynamically.

Ideally:

select 'question 2',t.c.query('node-name(@*)')
from @x.nodes('/rep') as t(c)

Would do that, but node-name doesn't seem to work in the RTM. I'm looking
into that...

It would also be how I solve Q3.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Eugene Kogan [MSFT]

2006-01-20, 8:24 pm

Re 2). It's local-name() and namespace-uri():

declare @x xml
set @x = '<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep><rep x1="z1"
x2="z2" x3="z3" x4="z4">some value</rep>'

select 'question 2',t.c.query('for $attr in @* return local-name($attr)')
from @x.nodes('/rep') as t(c)

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad742d96a8c7
ebfb801fe0fb@news.microsoft.com...
> Hello jbrozek@gmail.com,
>
>
> declare @x xml
> set @x = '<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep><rep
> x1="z1" x2="z2" x3="z3" x4="z4">some value</rep>'
> select 'question 1',t.c. value('count(@*)','i
nt')
> from @x.nodes('/rep') as t(c)
>
>
> Ideally:
>
> select 'question 2',t.c.query('node-name(@*)')
> from @x.nodes('/rep') as t(c)
>
> Would do that, but node-name doesn't seem to work in the RTM. I'm looking
> into that...
>
> It would also be how I solve Q3.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Kent Tegels

2006-01-20, 8:24 pm

Doh! Thanks Eugene!

So here's the ideal:

declare @x xml
set @x = '<rep x1="y1" x2="y2" x3="y3" x4="y4">some value</rep><rep x1="z1"
x2="z2" x3="z3" x4="z4">some value</rep>'
select top 1 t.c.query('for $attr in @* return local-name($attr)') from @x.nodes('/rep')
as t(c)
union all
select t.c.query('for $attr in @* return data($attr)') from @x.nodes('/rep')
as t(c)

Cheers!
Kent

Sponsored Links