ansaurus

Question

SQL Server 2008 XML query issue

Answer 1

+2 A:

You need to do something like this:

SELECT
    t.ID, -- or whatever you need from the table where the XML is located
    tbl.People.query('AdditionalInfoList')
FROM
    dbo.YourTable  t
CROSS APPLY
    t.(xmlcolumn).nodes('/People') AS Tbl(People)
WHERE
    tbl.People.value('(Item[@Name="Info"]/@Value)[1]', 'varchar(50)') LIKE '%Software Development%'
    OR
    tbl.People.value('(Item[@Name="Title"]/@Value)[1]', 'varchar(50)') LIKE '%Software Engineer%'

That should give you all the entries you're interested in.

Explanations:

The CROSS APPLY creates a "dummy" table which you need to give a name to - here: Tbl(People). What that name is doesn't really matter, and it's not case sensitive, so Tbl and tbl are identical.

If you want to Guid and the type as separate values from the <AdditionalInfoList>, you need to use this query here:

SELECT
    t.ID,
    --tbl.People.value('(Item[@Name="Info"]/@Value)[1]', 'varchar(50)') AS 'Info',
    --tbl.People.value('(Item[@Name="Title"]/@Value)[1]', 'varchar(50)') AS 'Title'
    Adtl.Info.value('(@Guid)[1]', 'varchar(50)') AS 'GUID',
    Adtl.Info.value('(@type)[1]', 'varchar(50)') AS 'Type'
FROM
    @table t
CROSS APPLY 
    t.xmlcolumn.nodes('/People') AS Tbl(People)
CROSS APPLY 
    Tbl.People.nodes('AdditionalInfoList/AdditionalInfoListItem') AS Adtl(Info)
WHERE
    Tbl.People.value('(Item[@Name="Info"]/@Value)[1]', 'varchar(50)') LIKE '%Software Development%'
    OR
    Tbl.People.value('(Item[@Name="Title"]/@Value)[1]', 'varchar(50)') LIKE '%Software Engineer%'

You basically have to do a second CROSS APPLY (that'll hurt your performance!) to get the "additional info" list for each of the entries from Tbl.Person and extract the Guid and the type value from that.

Check out the SQL Server 2005 XQuery and XML DML introduction - about in the middle of the article, there's a section on how to use the .nodes() function. Very helpful!

marc_s 2010-07-14 09:02:39

TBL is what? And how to get GUID and type from retrieved information for each matched row?

George2 2010-07-14 09:16:41

For TBL I am confused because you are using both TBL and "tbl".

George2 2010-07-14 09:17:34

Thank you, do you suppose your method is much faster than my previous solution, and why?

George2 2010-07-14 09:58:07

Thanks for your update. I am new to CROSS APPLY, I think Cross apply should join a table with a function, correct? But in your sample code, there is no function to join?

George2 2010-07-14 11:28:03

I have tried your first method (the first sample), and it works cool! My last question is, for this column you returned -- tbl.People.query('AdditionalInfoList'), from client side (e.g. ADO.Net which invokes store procedure) how to treat its type? As string?

George2 2010-07-14 12:08:37

@George2: I would believe this would be much faster, yes - and much more practical, too. Why? Because the XQuery stuff is quite nicely optimized in SQL Server, and being able to retrieve the actual values from the structures rather than parsing strings is just more robust, I think.

marc_s 2010-07-14 12:17:28

@George2: CROSS APPLY works against a set of data - e.g. a set of rows from your function, or the set of "pseudo-rows" from the XQuery `.nodes()` function - and that `.nodes()` is indeed a function, too! It returns "one row" for each entry in the XML that is matched by the XPath expression in the `.nodes()` function.

marc_s 2010-07-14 12:18:39

Thanks Marc, for the first sample, how to treat the returned column " tbl.People.query('AdditionalInfoList')"? Is it a string from ADO.Net client side or something else?

George2 2010-07-14 15:43:32

@George2: yes, you can read it back as a string, and then load it into a XmlDocument or XDocument (Linq-to-XML) - whatever works for you

marc_s 2010-07-14 15:51:02

Thanks, question answered!

George2 2010-07-14 15:59:53

ansaurus

tags:

views:

answers:

SQL Server 2008 XML query issue

related questions