Jacob's Blog - All Comments

re: FOR XML PATH - How to generate a Delimited String using FOR XML PATH?

Zubair Khalid — Thu, 04 Mar 2010 06:26:23 GMT

Thanks Alot for Knowledge sharing!

thats what i needed!

Zubair Khalid

re: XQuery Lab 51 – Querying an RDF document using TSQL and XQuery

Jacob Sebastian — Thu, 25 Feb 2010 09:15:32 GMT

Thanks Brad, I made another post showing your method. beyondrelational.com/.../XQuery-Lab-52-A-More-Intuitive-way-of-reading-an-RDF-document.aspx

re: Why do Excel 2007 prompt for repair when opening a document stored in SQL Server 2008 FILESTREAM Column?

Ritesh Shah — Thu, 18 Feb 2010 10:27:19 GMT

absolutely fabulous trouble shooting!!!!

re: XQuery Lab 51 – Querying an RDF document using TSQL and XQuery

Brad Schulz — Tue, 16 Feb 2010 23:59:28 GMT

Hi Jacob...

Good XML post.

The query could be re-worked to insert another CROSS APPLY to get the result columns and then you could use those column aliases in the WHERE clause. This may make the query clearer AND it involves less typing and duplication.

(The code may not be indented here as well as I'd like):

;WITH XMLNAMESPACES(

'http://www.w3.org/TR/RDF/' as r,

'http://purl.org/dc/elements/1. 0/' as d,

default 'http://dmoz.org/rdf/'

)

SELECT Resource, About, Title, CatID

FROM @x.nodes('RDF/Topic') a(x)

CROSS APPLY x.nodes('link') b(y)

CROSS APPLY @x.nodes('RDF/ExternalPage') c(z)

CROSS APPLY (SELECT y.value('@r:resource[1]','VARCHAR(100)') AS Resource,

z.value('@about','VARCHAR(100)') AS About,

z.value('d:Title[1]','VARCHAR(100)') AS Title,

x.value('catid[1]','VARCHAR(100)') AS CatID) F

WHERE Resource=About

re: XQuery Lab 23 - Retrieving values and position of elements

anand348 — Mon, 25 Jan 2010 17:41:00 GMT

Very interesting Post. I was looking for some thing like this for a very long time.

I am not sure if you have covered this already, but the above approach is very useful in handling comma seperated strings and implementing array like functions.

For Ex:

DECLARE @Value NVARCHAR(MAX)

SET @Value = '1,a,3,s,5,c,z,f'

SET @x = '<Names>' +

'<Name>' +

REPLACE( @Value, ',', '</Name><Name>') +

'</Name>' +

</Names>

Now, you have the XML used in your post.

BTW, can you cover few more functions like position(), if there are.

re: XQuery Lab 1 - Transforming rows to columns

Jacob Sebastian — Mon, 14 Dec 2009 10:37:47 GMT

does this help?

declare @t table (id int identity, data xml)

insert into @t (data)

select '

<fund>

</fund>

<fund>

</fund>

</source>

</accountorder>'

insert into @t (data)

select '

<fund>

</fund>

<fund>

</fund>

</source>

</accountorder>'

SELECT

p.value('.','INT') AS SourcePosition,

v.value('fundposition[1]','INT') AS fundposition,

v.value('percent[1]','INT') AS FundPercent

FROM @t

CROSS APPLY data.nodes('/accountorder/source') d(x)

CROSS APPLY x.nodes('sourceposition') s(p)

CROSS APPLY x.nodes('fund') AS f(v)

re: PASS Outstanding Volunteer Award

Jacob Sebastian — Sat, 12 Dec 2009 10:49:40 GMT

Thank you Pinal

re: PASS Outstanding Volunteer Award

Pinal Dave — Sat, 12 Dec 2009 07:52:13 GMT

Jacob,

First of all, Your hard work and identifying right people on to help you on team was tremendous job and you have successfully completed the same.

We all know how many hours and hours you have spend on community front.

You well deserved it and thank you for your good note to share your success with all of us.

Kind Regards,

Pinal

re: XQuery Lab 1 - Transforming rows to columns

mmorrow — Wed, 02 Dec 2009 18:04:38 GMT

That worked awesome!!! If I wanted to incorporate that into a query of a table called psr with a field called psr_xml, how would I do that?

With what you provided, I will be able to write a stored proc to pull a specified record, declare the xml variable, and return this result. However, I would like to parse XML from multiple records, as well.

Thanks again!

re: XQuery Lab 1 - Transforming rows to columns

Jacob Sebastian — Wed, 02 Dec 2009 02:48:48 GMT

Try this query:

SELECT

p.value('.','INT') AS SourcePosition,

v.value('fundposition[1]','INT') AS fundposition,

v.value('percent[1]','INT') AS FundPercent

FROM @x.nodes('/accountorder/source') d(x)

CROSS APPLY x.nodes('sourceposition') s(p)

CROSS APPLY x.nodes('fund') AS f(v)

re: XQuery Lab 1 - Transforming rows to columns

mmorrow — Tue, 01 Dec 2009 23:43:57 GMT

I have tried in vain to adapt your advice to this XML:

<fund>

</fund>

<fund>

</fund>

<fund>

</fund>

</source>

</accountorder>

I am trying to change the XML to a resultset like this:

sourceposition fundposition percent

1 1 25

1 2 25

1 3 50

If there are multiple source positions, I need those to display as additional rows, as well. This is the query that I tried, and I just get NULL values.

SELECT

x.value('(Field[@Name="sourceposition"]/@Value)[1]','VARCHAR(20)') AS sourceposition,

x.value('(Field[@Name="fundposition"]/@Value)[1]','VARCHAR(20)') AS fundposition,

x.value('(Field[@Name="percent"]/@Value)[1]','VARCHAR(20)') AS [percent]

FROM @x.nodes('/accountorder/source') d(x)

Thanks in advance for your help!

re: XQuery Lab 1 - Transforming rows to columns

mmorrow — Tue, 01 Dec 2009 23:43:57 GMT

I have tried in vain to adapt your advice to this XML:

<fund>

</fund>

<fund>

</fund>

<fund>

</fund>

</source>

</accountorder>

I am trying to change the XML to a resultset like this:

sourceposition fundposition percent

1 1 25

1 2 25

1 3 50

If there are multiple source positions, I need those to display as additional rows, as well. This is the query that I tried, and I just get NULL values.

SELECT

x.value('(Field[@Name="sourceposition"]/@Value)[1]','VARCHAR(20)') AS sourceposition,

x.value('(Field[@Name="fundposition"]/@Value)[1]','VARCHAR(20)') AS fundposition,

x.value('(Field[@Name="percent"]/@Value)[1]','VARCHAR(20)') AS [percent]

FROM @x.nodes('/accountorder/source') d(x)

Thanks in advance for your help!

re: Summary of my Experience at PASS 2009 Summit

Jacob Sebastian — Mon, 23 Nov 2009 12:15:41 GMT

Sure Ritesh!

re: Summary of my Experience at PASS 2009 Summit

Ritesh Shah — Mon, 23 Nov 2009 11:35:03 GMT

Article is really a great summary of pass summit but we are unfortunate enough that couldn't attend your session, can we have demo in any of the coming user group?

re: Summary of my Experience at PASS 2009 Summit

havejeet — Mon, 23 Nov 2009 09:53:44 GMT

Hi,

Thanks for sharing, keep it up!

regards,

Jeet