Extracting xml values using xquery

microsoft.public.sqlserver.xml

7 Oct 2007 2:47 AM

Hello All,

I am having problems extracting xml values from an xml document using
XQuery and would appreciate your help.

Here is the problem:

I am writting a stored procedure to insert data into a table like:

create table MyTable table (
 [id] [int] NOT NULL,
 [Prop1] [nvarchar](50) NULL,
 [Prop2] [int] NULL,
 [Prop3] [xml] NULL
)

create procedure MyProc( @Properties xml )
as
....

The parameter for the procedure is an xml document with a list of
properties in key-value format. I have added an example of the doc at
the end of this message.

My idea to implement this stored proc was to first shred the xml into
a table variable, and use the table to insert into MyTable. Something
like:

declare @tempTable (PropertyName nvarchar(50), ProvertyValue
nvarchar(max))
insert into @tempTable
 SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)')
PropertyName,
 T1.Props.value('(v/text())[1]', 'nvarchar(max)') PropertyValue
 FROM @Properties.nodel('/r/p') AS T1(Propsl)

Applying this to the xml below, I WOULD like to get
PropertyName PropertyValue
 id 1024
 Prop1 Property 1 value
 Prop2 42
 Prop3 <qqq> XML is cool </
qqq>

However, I always get NULL for the value of Prop3, which is an xml
document.

My questions:

1. What is the right way to extract xml values form an xml document
using XQuery? In the example
2. Is there a better way to implement my stored proc?

Kind regards
CD

For example,
<r>

 <k> id </k>
 <v> 1024 </v>


 <k > Prop1 </k>
 <v> Property 1 value </v>


 <k"> Prop2 </k>
 <v> 42 </v>


 <k > Prop3 </k>
 <v> <qqq> XML is cool </qqq> </v>

</r>

7 Oct 2007 7:24 AM

Mike C#

Do you need the <qqq> </qqq> tags?

DECLARE @x xml;
SET @x = N'<r>

<k> id </k>
<v> 1024 </v>


<k > Prop1 </k>
<v> Property 1 value </v>


<k> Prop2 </k>
<v> 42 </v>


<k > Prop3 </k>
<v> <qqq> XML is cool </qqq> </v>

</r>';

SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
FROM @x.nodes('/r/p') AS T1(Props)

Show quote

"CD" <crb***@yahoo.com> wrote in message
news:1191725264.865657.28510@g4g2000hsf.googlegroups.com...
> Hello All,
>
> I am having problems extracting xml values from an xml document using
> XQuery and would appreciate your help.
>
> Here is the problem:
>
> I am writting a stored procedure to insert data into a table like:
>
> create table MyTable table (
> [id] [int] NOT NULL,
> [Prop1] [nvarchar](50) NULL,
> [Prop2] [int] NULL,
> [Prop3] [xml] NULL
> )
>
> create procedure MyProc( @Properties xml )
> as
> ...
>
> The parameter for the procedure is an xml document with a list of
> properties in key-value format. I have added an example of the doc at
> the end of this message.
>
> My idea to implement this stored proc was to first shred the xml into
> a table variable, and use the table to insert into MyTable. Something
> like:
>
> declare @tempTable (PropertyName nvarchar(50), ProvertyValue
> nvarchar(max))
> insert into @tempTable
> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)')
> PropertyName,
> T1.Props.value('(v/text())[1]', 'nvarchar(max)') PropertyValue
> FROM @Properties.nodel('/r/p') AS T1(Propsl)
>
> Applying this to the xml below, I WOULD like to get
> PropertyName PropertyValue
> id 1024
> Prop1 Property 1 value
> Prop2 42
> Prop3 <qqq> XML is cool </
> qqq>
>
> However, I always get NULL for the value of Prop3, which is an xml
> document.
>
> My questions:
>
> 1. What is the right way to extract xml values form an xml document
> using XQuery? In the example
> 2. Is there a better way to implement my stored proc?
>
> Kind regards
> CD
>
> For example,
> <r>
> 
> <k> id </k>
> <v> 1024 </v>
> 
> 
> <k > Prop1 </k>
> <v> Property 1 value </v>
> 
> 
> <k"> Prop2 </k>
> <v> 42 </v>
> 
> 
> <k > Prop3 </k>
> <v> <qqq> XML is cool </qqq> </v>
> 
> </r>
>

7 Oct 2007 2:26 PM

Hello Mike,

Thank you for your reply.

Yes. I need the <qqq> </qqq> tags. In fact, the xml between the <v> </
v> tags can be complex, as in the example below, where I modified your
sample xml by adding a new key-value pair. In that case, the value
returned was NULL.

Any ideas?

DECLARE @x xml;
SET @x = N'<r>

<k> id </k>
<v> 1024 </v>


<k > Prop1 </k>
<v> Property 1 value </v>


<k> Prop2 </k>
<v> 42 </v>


<k > Prop3 </k>
<v> <qqq> XML is cool </qqq> </v>


<k>Address</k>
 <v xmlns="http://schemas.usps.gov/address/2005/04">
 <address>
 <address1>17223 SW Main St.</address1>
 <address2>Apt. 33</address2>
 <city>Good city</city>
 <state>JK</state>
 <zip>47908</zip>
 </address>
 </v>

</r>';

SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
 T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
FROM @x.nodes('/r/p') AS T1(Props)

Kind regards
-CD

Show quote

On Oct 7, 12:24 am, "Mike C#" <x***@xyz.com> wrote:
> Do you need the <qqq> </qqq> tags?
>
> DECLARE @x xml;
> SET @x = N'<r>
> 
> <k> id </k>
> <v> 1024 </v>
> 
> 
> <k > Prop1 </k>
> <v> Property 1 value </v>
> 
> 
> <k> Prop2 </k>
> <v> 42 </v>
> 
> 
> <k > Prop3 </k>
> <v> <qqq> XML is cool </qqq> </v>
> 
> </r>';
>
> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
> T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
> FROM @x.nodes('/r/p') AS T1(Props)
>
> "CD" <crb***@yahoo.com> wrote in message
>
> news:1191725264.865657.28510@g4g2000hsf.googlegroups.com...
>
>
>
> > Hello All,
>
> > I am having problems extracting xml values from an xml document using
> > XQuery and would appreciate your help.
>
> > Here is the problem:
>
> > I am writting a stored procedure to insert data into a table like:
>
> > create table MyTable table (
> > [id] [int] NOT NULL,
> > [Prop1] [nvarchar](50) NULL,
> > [Prop2] [int] NULL,
> > [Prop3] [xml] NULL
> > )
>
> > create procedure MyProc( @Properties xml )
> > as
> > ...
>
> > The parameter for the procedure is an xml document with a list of
> > properties in key-value format. I have added an example of the doc at
> > the end of this message.
>
> > My idea to implement this stored proc was to first shred the xml into
> > a table variable, and use the table to insert into MyTable. Something
> > like:
>
> > declare @tempTable (PropertyName nvarchar(50), ProvertyValue
> > nvarchar(max))
> > insert into @tempTable
> > SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)')
> > PropertyName,
> > T1.Props.value('(v/text())[1]', 'nvarchar(max)') PropertyValue
> > FROM @Properties.nodel('/r/p') AS T1(Propsl)
>
> > Applying this to the xml below, I WOULD like to get
> > PropertyName PropertyValue
> > id 1024
> > Prop1 Property 1 value
> > Prop2 42
> > Prop3 <qqq> XML is cool </
> > qqq>
>
> > However, I always get NULL for the value of Prop3, which is an xml
> > document.
>
> > My questions:
>
> > 1. What is the right way to extract xml values form an xml document
> > using XQuery? In the example
> > 2. Is there a better way to implement my stored proc?
>
> > Kind regards
> > CD
>
> > For example,
> > <r>
> > 
> > <k> id </k>
> > <v> 1024 </v>
> > 
> > 
> > <k > Prop1 </k>
> > <v> Property 1 value </v>
> > 
> > 
> > <k"> Prop2 </k>
> > <v> 42 </v>
> > 
> > 
> > <k > Prop3 </k>
> > <v> <qqq> XML is cool </qqq> </v>
> > 
> > </r>- Hide quoted text -
>
> - Show quoted text -

8 Oct 2007 12:38 AM

Mike C#

You can grab the <v>...</v> nodes which will include all nodes contained,

using .query(). The reason your "complex" <address> nodes are being

returned as NULL is because the parent <v> node has a different namespace.
Try this:

DECLARE @x xml;
SET @x = N'<r>

<k> id </k>
<v> 1024 </v>


<k > Prop1 </k>
<v> Property 1 value </v>


<k> Prop2 </k>
<v> 42 </v>


<k > Prop3 </k>
<v> <qqq> XML is cool </qqq> </v>


<k>Address</k>
<v xmlns="http://schemas.usps.gov/address/2005/04">
<address>
<address1>17223 SW Main St.</address1>
<address2>Apt. 33</address2>
<city>Good city</city>
<state>JK</state>
<zip>47908</zip>
</address>
</v>

</r>';

SELECT T1.Props.value('(k)[1]', 'nvarchar(50)') PropertyName,
T1.Props.query('(*:v)[1]') PropertyValue
FROM @x.nodes('/r/p') AS T1(Props)

Show quote

"CD" <crb***@yahoo.com> wrote in message
news:1191767174.768374.257160@57g2000hsv.googlegroups.com...
> Hello Mike,
>
> Thank you for your reply.
>
> Yes. I need the <qqq> </qqq> tags. In fact, the xml between the <v> </
> v> tags can be complex, as in the example below, where I modified your
> sample xml by adding a new key-value pair. In that case, the value
> returned was NULL.
>
> Any ideas?
>
>
> DECLARE @x xml;
> SET @x = N'<r>
> 
> <k> id </k>
> <v> 1024 </v>
> 
> 
> <k > Prop1 </k>
> <v> Property 1 value </v>
> 
> 
> <k> Prop2 </k>
> <v> 42 </v>
> 
> 
> <k > Prop3 </k>
> <v> <qqq> XML is cool </qqq> </v>
> 
> 
> <k>Address</k>
> <v xmlns="http://schemas.usps.gov/address/2005/04">
> <address>
> <address1>17223 SW Main St.</address1>
> <address2>Apt. 33</address2>
> <city>Good city</city>
> <state>JK</state>
> <zip>47908</zip>
> </address>
> </v>
> 
> </r>';
>
>
> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
> T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
> FROM @x.nodes('/r/p') AS T1(Props)
>
> Kind regards
> -CD
>
> On Oct 7, 12:24 am, "Mike C#" <x***@xyz.com> wrote:
>> Do you need the <qqq> </qqq> tags?
>>
>> DECLARE @x xml;
>> SET @x = N'<r>
>> 
>> <k> id </k>
>> <v> 1024 </v>
>> 
>> 
>> <k > Prop1 </k>
>> <v> Property 1 value </v>
>> 
>> 
>> <k> Prop2 </k>
>> <v> 42 </v>
>> 
>> 
>> <k > Prop3 </k>
>> <v> <qqq> XML is cool </qqq> </v>
>> 
>> </r>';
>>
>> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
>> T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
>> FROM @x.nodes('/r/p') AS T1(Props)
>>
>> "CD" <crb***@yahoo.com> wrote in message
>>
>> news:1191725264.865657.28510@g4g2000hsf.googlegroups.com...
>>
>>
>>
>> > Hello All,
>>
>> > I am having problems extracting xml values from an xml document using
>> > XQuery and would appreciate your help.
>>
>> > Here is the problem:
>>
>> > I am writting a stored procedure to insert data into a table like:
>>
>> > create table MyTable table (
>> > [id] [int] NOT NULL,
>> > [Prop1] [nvarchar](50) NULL,
>> > [Prop2] [int] NULL,
>> > [Prop3] [xml] NULL
>> > )
>>
>> > create procedure MyProc( @Properties xml )
>> > as
>> > ...
>>
>> > The parameter for the procedure is an xml document with a list of
>> > properties in key-value format. I have added an example of the doc at
>> > the end of this message.
>>
>> > My idea to implement this stored proc was to first shred the xml into
>> > a table variable, and use the table to insert into MyTable. Something
>> > like:
>>
>> > declare @tempTable (PropertyName nvarchar(50), ProvertyValue
>> > nvarchar(max))
>> > insert into @tempTable
>> > SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)')
>> > PropertyName,
>> > T1.Props.value('(v/text())[1]', 'nvarchar(max)') PropertyValue
>> > FROM @Properties.nodel('/r/p') AS T1(Propsl)
>>
>> > Applying this to the xml below, I WOULD like to get
>> > PropertyName PropertyValue
>> > id 1024
>> > Prop1 Property 1 value
>> > Prop2 42
>> > Prop3 <qqq> XML is cool </
>> > qqq>
>>
>> > However, I always get NULL for the value of Prop3, which is an xml
>> > document.
>>
>> > My questions:
>>
>> > 1. What is the right way to extract xml values form an xml document
>> > using XQuery? In the example
>> > 2. Is there a better way to implement my stored proc?
>>
>> > Kind regards
>> > CD
>>
>> > For example,
>> > <r>
>> > 
>> > <k> id </k>
>> > <v> 1024 </v>
>> > 
>> > 
>> > <k > Prop1 </k>
>> > <v> Property 1 value </v>
>> > 
>> > 
>> > <k"> Prop2 </k>
>> > <v> 42 </v>
>> > 
>> > 
>> > <k > Prop3 </k>
>> > <v> <qqq> XML is cool </qqq> </v>
>> > 
>> > </r>- Hide quoted text -
>>
>> - Show quoted text -
>
>

25 Oct 2007 10:26 PM

Peter DeBetta

A slight change to Mike's code that removes the <v> node from the
PropertyValue return column...

SELECT
 T1.Props.value('(k/text())[1]', 'nvarchar(50)') AS PropertyName,
 CASE
 WHEN T1.Props.query('(*:v)[1]').exist('./*/*') = 1
 THEN T1.Props.query('(*:v)[1]/*')
 ELSE T1.Props.query('(*:v)[1]/text()')
 END AS PropertyValue
FROM @x.nodes('/r/p') AS T1(Props)

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com

Show quote

"Mike C#" <x**@xyz.com> wrote in message
news:egvihOUCIHA.3848@TK2MSFTNGP05.phx.gbl...
> You can grab the <v>...</v> nodes which will include all nodes contained,
> using .query(). The reason your "complex" <address> nodes are being
> returned as NULL is because the parent <v> node has a different namespace.
> Try this:
>
> DECLARE @x xml;
> SET @x = N'<r>
> 
> <k> id </k>
> <v> 1024 </v>
> 
> 
> <k > Prop1 </k>
> <v> Property 1 value </v>
> 
> 
> <k> Prop2 </k>
> <v> 42 </v>
> 
> 
> <k > Prop3 </k>
> <v> <qqq> XML is cool </qqq> </v>
> 
> 
> <k>Address</k>
> <v xmlns="http://schemas.usps.gov/address/2005/04">
> <address>
> <address1>17223 SW Main St.</address1>
> <address2>Apt. 33</address2>
> <city>Good city</city>
> <state>JK</state>
> <zip>47908</zip>
> </address>
> </v>
> 
> </r>';
>
> SELECT T1.Props.value('(k)[1]', 'nvarchar(50)') PropertyName,
> T1.Props.query('(*:v)[1]') PropertyValue
> FROM @x.nodes('/r/p') AS T1(Props)
>
> "CD" <crb***@yahoo.com> wrote in message
> news:1191767174.768374.257160@57g2000hsv.googlegroups.com...
>> Hello Mike,
>>
>> Thank you for your reply.
>>
>> Yes. I need the <qqq> </qqq> tags. In fact, the xml between the <v> </
>> v> tags can be complex, as in the example below, where I modified your
>> sample xml by adding a new key-value pair. In that case, the value
>> returned was NULL.
>>
>> Any ideas?
>>
>>
>> DECLARE @x xml;
>> SET @x = N'<r>
>> 
>> <k> id </k>
>> <v> 1024 </v>
>> 
>> 
>> <k > Prop1 </k>
>> <v> Property 1 value </v>
>> 
>> 
>> <k> Prop2 </k>
>> <v> 42 </v>
>> 
>> 
>> <k > Prop3 </k>
>> <v> <qqq> XML is cool </qqq> </v>
>> 
>> 
>> <k>Address</k>
>> <v xmlns="http://schemas.usps.gov/address/2005/04">
>> <address>
>> <address1>17223 SW Main St.</address1>
>> <address2>Apt. 33</address2>
>> <city>Good city</city>
>> <state>JK</state>
>> <zip>47908</zip>
>> </address>
>> </v>
>> 
>> </r>';
>>
>>
>> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
>> T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
>> FROM @x.nodes('/r/p') AS T1(Props)
>>
>> Kind regards
>> -CD
>>
>> On Oct 7, 12:24 am, "Mike C#" <x***@xyz.com> wrote:
>>> Do you need the <qqq> </qqq> tags?
>>>
>>> DECLARE @x xml;
>>> SET @x = N'<r>
>>> 
>>> <k> id </k>
>>> <v> 1024 </v>
>>> 
>>> 
>>> <k > Prop1 </k>
>>> <v> Property 1 value </v>
>>> 
>>> 
>>> <k> Prop2 </k>
>>> <v> 42 </v>
>>> 
>>> 
>>> <k > Prop3 </k>
>>> <v> <qqq> XML is cool </qqq> </v>
>>> 
>>> </r>';
>>>
>>> SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)') PropertyName,
>>> T1.Props.value('(v)[1]', 'nvarchar(max)') PropertyValue
>>> FROM @x.nodes('/r/p') AS T1(Props)
>>>
>>> "CD" <crb***@yahoo.com> wrote in message
>>>
>>> news:1191725264.865657.28510@g4g2000hsf.googlegroups.com...
>>>
>>>
>>>
>>> > Hello All,
>>>
>>> > I am having problems extracting xml values from an xml document using
>>> > XQuery and would appreciate your help.
>>>
>>> > Here is the problem:
>>>
>>> > I am writting a stored procedure to insert data into a table like:
>>>
>>> > create table MyTable table (
>>> > [id] [int] NOT NULL,
>>> > [Prop1] [nvarchar](50) NULL,
>>> > [Prop2] [int] NULL,
>>> > [Prop3] [xml] NULL
>>> > )
>>>
>>> > create procedure MyProc( @Properties xml )
>>> > as
>>> > ...
>>>
>>> > The parameter for the procedure is an xml document with a list of
>>> > properties in key-value format. I have added an example of the doc at
>>> > the end of this message.
>>>
>>> > My idea to implement this stored proc was to first shred the xml into
>>> > a table variable, and use the table to insert into MyTable. Something
>>> > like:
>>>
>>> > declare @tempTable (PropertyName nvarchar(50), ProvertyValue
>>> > nvarchar(max))
>>> > insert into @tempTable
>>> > SELECT T1.Props.value('(k/text())[1]', 'nvarchar(50)')
>>> > PropertyName,
>>> > T1.Props.value('(v/text())[1]', 'nvarchar(max)') PropertyValue
>>> > FROM @Properties.nodel('/r/p') AS T1(Propsl)
>>>
>>> > Applying this to the xml below, I WOULD like to get
>>> > PropertyName PropertyValue
>>> > id 1024
>>> > Prop1 Property 1 value
>>> > Prop2 42
>>> > Prop3 <qqq> XML is cool </
>>> > qqq>
>>>
>>> > However, I always get NULL for the value of Prop3, which is an xml
>>> > document.
>>>
>>> > My questions:
>>>
>>> > 1. What is the right way to extract xml values form an xml document
>>> > using XQuery? In the example
>>> > 2. Is there a better way to implement my stored proc?
>>>
>>> > Kind regards
>>> > CD
>>>
>>> > For example,
>>> > <r>
>>> > 
>>> > <k> id </k>
>>> > <v> 1024 </v>
>>> > 
>>> > 
>>> > <k > Prop1 </k>
>>> > <v> Property 1 value </v>
>>> > 
>>> > 
>>> > <k"> Prop2 </k>
>>> > <v> 42 </v>
>>> > 
>>> > 
>>> > <k > Prop3 </k>
>>> > <v> <qqq> XML is cool </qqq> </v>
>>> > 
>>> > </r>- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>>
>
>