I have an XML document that looks like the following:

<record>
 <Field1>value1</Field1>
 <Field2>value2</Field2>
 <Field3>value3</Field3>
</record>

What I'm aiming to do is to show the data in a report as in the following 
example:

record:
               Field1               value1
               Field2               value2
               Field3               value3

The "Field1-3"-elements can have any name, so I'm trying to get a table that 
has in one result column the element name, and in the second column the 
element value. How can I achieve this?

I'm primarely interested in the SQL script.

CJ
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html


"CJ" <CJ@discussions.microsoft.com> wrote in message 
news:212B03EA-BADD-4D0E-A54D-762F6EE4B03C@microsoft.com...
>I have an XML document that looks like the following:
>
> <record>
> <Field1>value1</Field1>
> <Field2>value2</Field2>
> <Field3>value3</Field3>
> </record>
>
> What I'm aiming to do is to show the data in a report as in the following
> example:
>
> record:
>               Field1               value1
>               Field2               value2
>               Field3               value3
>
> The "Field1-3"-elements can have any name, so I'm trying to get a table 
> that
> has in one result column the element name, and in the second column the
> element value. How can I achieve this?
>
> I'm primarely interested in the SQL script. 

He only talks about getting the values of an XML document, not about getting 
the names. But I saw that I can use Xpath, so here is something that works 
for this specific case:



DECLARE @data AS XML
SET @data =
N’
<result>
<Field1>Value1</Field1>
<Field2>Value2</Field2>
<Field3>Value3</Field3>
</result>’

SELECT
result.data.query(‘local-name(.)’) AS ResultName,
resultfield.data.query(‘local-name(.)’).value(‘.’, ‘VARCHAR(MAX)’) AS 
FieldName,
resultfield.data.query(‘.’).value(‘.’, ‘VARCHAR(MAX)’) AS FieldValue,
FROM @data.nodes(‘result/.’) AS result(data)
	CROSS JOIN @data.nodes(‘result/*’) AS resultfield(data)


And there we are. Using a CROSS JOIN didn't give me any wrong results, so I 
guess I can go with that for now.



"Uri Dimant" wrote:

> CJ
> http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html
> 
> 
> "CJ" <CJ@discussions.microsoft.com> wrote in message 
> news:212B03EA-BADD-4D0E-A54D-762F6EE4B03C@microsoft.com...
> >I have an XML document that looks like the following:
> >
> > <record>
> > <Field1>value1</Field1>
> > <Field2>value2</Field2>
> > <Field3>value3</Field3>
> > </record>
> >
> > What I'm aiming to do is to show the data in a report as in the following
> > example:
> >
> > record:
> >               Field1               value1
> >               Field2               value2
> >               Field3               value3
> >
> > The "Field1-3"-elements can have any name, so I'm trying to get a table 
> > that
> > has in one result column the element name, and in the second column the
> > element value. How can I achieve this?
> >
> > I'm primarely interested in the SQL script. 
> 
> 
> .
>