Prev: Replication from SQL2008 to SQL2005
Next: 2008R2 Views with ORDER BY clause
From: laredotornado on 21 May 2010 12:34
Hi,

I'm running SQL Server 2005. One of the columns in my view is called
"category_xml" and contains data similar to the following ...

<categories>
<info_type id="3" name="Restaurant">
<category id="13" name="Steakhouse"/>
<category id="14" name="Seafood"/>
</info_type>
</categories>

I am trying to run a query to say give me results that have a
"category" element who has a name attribute that matches "x". How do
I construct the SQL for such a query?

Thanks, - Dave
From: John Bell on 21 May 2010 15:36
On Fri, 21 May 2010 09:34:04 -0700 (PDT), laredotornado
<laredotornado(a)zipmail.com> wrote:

>Hi,
>
>I'm running SQL Server 2005. One of the columns in my view is called
>"category_xml" and contains data similar to the following ...
>
><categories>
> <info_type id="3" name="Restaurant">
> <category id="13" name="Steakhouse"/>
> <category id="14" name="Seafood"/>
> </info_type>
></categories>
>
>I am trying to run a query to say give me results that have a
>"category" element who has a name attribute that matches "x". How do
>I construct the SQL for such a query?
>
>Thanks, - Dave

Hi Dave

One way...

DECLARE @xml XML

SET @xml = '<categories>
<info_type id="3" name="Restaurant">
<category id="13" name="Steakhouse"/>
<category id="14" name="Seafood"/>
</info_type>
<info_type id="2" name="Hotel">
<category id="15" name="French"/>
<category id="16" name="Seafood"/>
</info_type>
</categories>' ;

SELECT node.value('@id','int'),
node.value('@name','varchar(20)')
FROM @xml.nodes('//categories/info_type/category') AS category(node)
WHERE node.value('@name','varchar(20)') = 'Seafood'


CREATE TABLE xmltbl ( xmlcol XML )

INSERT INTO xmltbl ( xmlcol )
VALUES (
'<categories>
<info_type id="3" name="Restaurant">
<category id="13" name="Steakhouse"/>
<category id="14" name="Seafood"/>
</info_type>
</categories>' ) ;

INSERT INTO xmltbl ( xmlcol )
VALUES (
'<categories>
<info_type id="2" name="Hotel">
<category id="15" name="French"/>
<category id="16" name="Seafood"/>
</info_type>
</categories>' ) ;

SELECT category.node.value('@id','int'),
category.node.value('@name','varchar(20)')
FROM xmltbl
CROSS APPLY xmlcol.nodes('//categories/info_type/category') AS
category(node)
WHERE category.node.value('@name','varchar(20)') = 'Seafood'

John
From: Plamen Ratchev on 21 May 2010 18:49
Here is another method:

SELECT category.node.value('@id','int'),
category.node.value('@name','varchar(20)')
FROM xmltbl
CROSS APPLY
xmlcol.nodes('//categories/info_type/category[@name="Seafood"]') AS
category(node);

--
Plamen Ratchev
http://www.SQLStudio.com
From: John Bell on 22 May 2010 06:35
On Fri, 21 May 2010 18:49:41 -0400, Plamen Ratchev
<Plamen(a)SQLStudio.com> wrote:

>Here is another method:
>
>SELECT category.node.value('@id','int'),
> category.node.value('@name','varchar(20)')
>FROM xmltbl
>CROSS APPLY
>xmlcol.nodes('//categories/info_type/category[@name="Seafood"]') AS
>category(node);

And to use a variable...

DECLARE @param varchar(20)

SET @param = 'Seafood'

SELECT category.node.value('@id','int'),
category.node.value('@name','varchar(20)')
FROM xmltbl
CROSS APPLY
xmlcol.nodes('//categories/info_type/category[@name=sql:variable("@param")]')
AS category(node)

:)

John
 | 
Pages: 1
Prev: Replication from SQL2008 to SQL2005
Next: 2008R2 Views with ORDER BY clause