Optimizing SELECT query

Hi

I have one table (tableDemo) with following structure:

TSID bigint (Primary Key)
TskID bigint
Sequence bigint
Version bigint
Frequency varchar(500)
WOID bigint
DateSchedule datetime
TimeStandard real
MeterEstimated real
MeterLast real
Description ntext
CreatedBy bigint
CreatedDate datetime
ModifiedBy bigint
ModifiedDate datetime
Id uniqueidentifier

I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.

Is there any techniques where I can access all these records within 2-3 seconds ?

Regards,

ND

Why do you need every field of every record? Surely you are not going to display them all on one page? If you want to use them for paging, and are using Sql Server 2005, have a look at ROW_NUMBER:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

|||

Are there any large files stored in any of the rows being returned? i.e. files or larges amounts of text? Returning only 8000 rows shouldnt take very long esp not 120 seconds. Is the instance of sql on your local machine or located someone else?

Tim

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <= Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.
> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.
> 3) Is the BETWEEN operator slower than >= and <=> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> >
> > 1) Is it reasonable to expect improved performance by putting the
> > clustered index on the field you most use for RANGE searches.
> Most likely, yes.
> > 2) Is there any reason you wouldn't create a clustered index on a
> > DateTime field, if the data in that field was autogenerated by
> > the server clock and moved only forward chronologically?
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
> > 3) Is the BETWEEN operator slower than >= and <=> > Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> > faster or slower than this ...
> > CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>sql

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <=
Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.

> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.

> 3) Is the BETWEEN operator slower than >= and <=
> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> Most likely, yes.
>
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
>
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>

Optimiser problem? Takes ages to retrieve next key in 4-segment

Nikki,
You are absolutely correct in your view of this, but unfortunately,
I don't think there is a way in T-SQL to deal with this. It might
become easier with the analytic functions of SQL Server 2005, but
that may not be true and doesn't help you much now... It may also
be that there is a clever way of doing this with cursors that I'm not
seeing, but it would be a shame to have to do that.
If it weren't for the datetime column, you could create (and perhaps
get away with indexing) a computed table column or view column that
concatenated fixed-length string versions of the index columns. Datetime
conversions are considered non-deterministic or imprecise and can't be
indexed, so you would have to use a decimal column or separate serial
date and time columns stored as integers or strings.
Not fun, but if this is a serious concern for you, it might be worth
the trouble of considering. The concatenation is also the closest you can
come to a "clean" way of expressing this, but it won't be as efficient as
it should be:
select top 1 *
from [Requirement Detail]
WHERE
ORDER_11
+ CONVERT(PRTNUM_11,15)
+ CONVERT(CHAR(30),CURDUE_11,121)
+ TYPE_11
>
@.ORDER_11
+ CONVERT(@.PRTNUM_11,15)
+ CONVERT(CHAR(30),@.CURDUE_11,121)
+ @.TYPE_11
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
You might get lucky with this, depending on the
distribution of data you have. Adding this or other
conditions you know can use indexes and are true can
help:
ORDER_11 >= @.ORDER_11
At the risk of increasing your frustration more than highlighting your sense
, I'll
point out that if SQL Server implemented row constructors according
to the ANSI SQL standard, it would be very easy:
-- WARNING: DON'T TRY THIS IN T-SQL. :(
-- USING T-SQL TOP also:
SELECT TOP 1 *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
ANSI SQL doesn't have TOP, and I don't have my copy handy
to be sure MAX can be used with row constructors, but the
ANSI version might be one of these:
SELECT *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11) = (
SELECT MIN((ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11))
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
)
or,
SELECT *
FROM [Requirement Detail] AS RD1
WHERE NOT EXISTS (
SELECT * FROM [Requirement Detail] AS RD2
WHERE (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
AND (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
< (RD1.ORDER_11, RD1.PRTNUM_11, RD1.CURDUE_11, RD1.TYPE_11)
)
SK
Nikki Locke wrote:

> Thanks for your reply.
> Unfortunately clustered indexes are out. If we did have a clustered index,
> it would be on the most frequently used key (which is not this one,
> unfortunately).
> Is there a better way or wording the query? All I want it to do is to make
> a key out of the 4 fields provided, look the key up in the index, and
> return either the found record, or the next record in the index if the
> specified one doesn't exist.
> When stated like that, it is obviously a very cheap operation. If I could
> only find a way of telling SQL server that was what I wanted, all would be
> fine.
>SQL Server does support ALL, but Hugo may have been (and I certainly
was) lamenting the fact that SQL Server doesn't support multicolumn
comparisons of the sort (a1,b1,c1) < (a2,b2,c2). Queries that use
ALL can fairly easily be rewritten without ALL, but queries that use
multicolumn comparisons cannot, and the rewrite, in this case
(
(a1 < a2)
OR
((a1 = a2) and (b1 < b2))
OR
((a1 = a2) and (b1 = b2) and (c1 < c2))
)
is not (that I've seen) optimized to take advantage of an index
on (a,b,c), which ought to help out here.
SK
oj wrote:

> Hugo,
> I haven't been following the entire thread. But sqlserver doe support ALL
> (to some extend).
> http://msdn.microsoft.com/library/e..._qd_11_1sz0.asp
>|||Sounds good! Thanks for the followup.
SK
Nikki Locke wrote:

>Thankyou very much for your detailed and useful reply.
>I now have a much better understanding of the problem, and can start to
>approach it from a different angle.
>I already have a query which executes in 3 msecs (as opposed to 3 secs for
>the original), which is as follows...
>declare @.ORDER_11 nchar(10)
>declare @.PRTNUM_11 nvarchar(15)
>declare @.CURDUE_11 smalldatetime
>declare @.TYPE_11 nchar(2)
>declare @.KEY nchar(35)
>set @.ORDER_11 = '5480184500'
>set @.PRTNUM_11 = '548000000000000'
>set @.CURDUE_11 = '2005-04-20 12:00:00'
>set @.TYPE_11 = 'RQ'
>set @.KEY = @.ORDER_11 + @.PRTNUM_11 + CONVERT(nchar(8), @.CURDUE_11, 112) +
>@.TYPE_11
> SELECT TOP 1
> UNQKEY_11,
> Convert(Money, TIMESTAMP_11),
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
> FROM
> [dbo].[Requirement Detail]
> WHERE
> ((ORDER_11>=@.ORDER_11)) -- the major part of the key
> AND
> ORDER_11 + PRTNUM_11 + CONVERT(nchar(8), CURDUE_11, 112) + TYPE_11
>
> ORDER BY
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
>GO
>As far as I can see, provided there aren't thousands of rows where
>ORDER_11>=@.ORDER_11 but the rest of the condition is not satisfied, this is
>pretty optimal. But I'm still going to fiddle to see if I can improve the
>original query to cut down on the amount of rewriting I have to do on the
>stored procedures (there are hundreds of them!).
>[Aside] I used style 113 and length 8 for the date query because I happen
>to know the time part of the date is not significant in the real data.
>
>

OPENXML Question

Hello,
This is my XML data:
<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>
How would I use OPENXML to get the data in this format:
COLUMN1COLUMN2COLUMN3COLUMN4
-- -- -- -- --
aBCd
Thanks.
Hi
Try something like:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
N'<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>'
-- Use OPENXML to provide rowset consisting of customer data.
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
) A
EXEC sp_xml_removedocument @.hdoc
The subquery and max functions are because you want to pivot the output. If
you can work with
SELECT [KEY], [value]
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
transformation.
John
"Ganesh Muthuvelu" wrote:

> Hello,
> This is my XML data:
> <RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>
> How would I use OPENXML to get the data in this format:
> COLUMN1COLUMN2COLUMN3COLUMN4
> -- -- -- -- --
> aBCd
> Thanks.
|||John,
Thanks for the reply. Could you tell me what this means in your query:?
WITH ( [KEY] char(7), value char(1) '.' )
I understand that the [KEY] and value are columns of char(7) and char(1) ,
but what does the '.' mean?.
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
Thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Try something like:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> N'<RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>'
> -- Use OPENXML to provide rowset consisting of customer data.
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> ) A
> EXEC sp_xml_removedocument @.hdoc
> The subquery and max functions are because you want to pivot the output. If
> you can work with
> SELECT [KEY], [value]
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> transformation.
> John
> "Ganesh Muthuvelu" wrote:
|||Hi
'.' is an abbreviation for self::node() Check out "Specifying a Node Test in
the Location Path" in Books online.
I could have written:
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) 'self::node()' )
) A
John
"Ganesh Muthuvelu" wrote:
[vbcol=seagreen]
> John,
> Thanks for the reply. Could you tell me what this means in your query:?
> WITH ( [KEY] char(7), value char(1) '.' )
> I understand that the [KEY] and value are columns of char(7) and char(1) ,
> but what does the '.' mean?.
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> Thanks.
>
>
> "John Bell" wrote:
|||John,
Thanks again, Could you also see the second part of the question.
*************
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some
sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
************
Thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> '.' is an abbreviation for self::node() Check out "Specifying a Node Test in
> the Location Path" in Books online.
> I could have written:
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) 'self::node()' )
> ) A
>
> John
> "Ganesh Muthuvelu" wrote:
|||Hi
Your XML does not lend itself to pivoting if you have multiple response
segments. You would probably need to load it into a temporary table where you
can create an artificial key and then generate the pivot from that.
Alternatively you may be able to use a transform and the position function to
achieve something similar. If you pivoted on the client you would not need
this extra step.
If you had multiple response segments, you would need a new root node.
HTH
John
"Ganesh Muthuvelu" wrote:
[vbcol=seagreen]
> John,
> Thanks again, Could you also see the second part of the question.
> *************
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some
> sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> ************
> Thanks.
> "John Bell" wrote:

OPENXML Question

Hello,
This is my XML data:
<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>
How would I use OPENXML to get the data in this format:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
-- -- -- -- --
a B C d
Thanks.Hi
Try something like:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
N'<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>'
-- Use OPENXML to provide rowset consisting of customer data.
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), val
ue
char(1) '.' )
) A
EXEC sp_xml_removedocument @.hdoc
The subquery and max functions are because you want to pivot the output. If
you can work with
SELECT [KEY], [value]
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), val
ue
char(1) '.' )
it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
transformation.
John
"Ganesh Muthuvelu" wrote:

> Hello,
> This is my XML data:
> <RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>
> How would I use OPENXML to get the data in this format:
> COLUMN1 COLUMN2 COLUMN3 COLUMN4
> -- -- -- -- --
> a B C d
> Thanks.|||John,
Thanks for the reply. Could you tell me what this means in your query:?
WITH ( [KEY] char(7), value char(1) '.' )
I understand that the [KEY] and value are columns of char(7) and char(1)
,
but what does the '.' mean?.
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some sor
t
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
Thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Try something like:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> N'<RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>'
> -- Use OPENXML to provide rowset consisting of customer data.
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), v
alue
> char(1) '.' )
> ) A
> EXEC sp_xml_removedocument @.hdoc
> The subquery and max functions are because you want to pivot the output. I
f
> you can work with
> SELECT [KEY], [value]
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), v
alue
> char(1) '.' )
> it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> transformation.
> John
> "Ganesh Muthuvelu" wrote:
>|||Hi
'.' is an abbreviation for self::node() Check out "Specifying a Node Test in
the Location Path" in Books online.
I could have written:
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), val
ue
char(1) 'self::node()' )
) A
John
"Ganesh Muthuvelu" wrote:
[vbcol=seagreen]
> John,
> Thanks for the reply. Could you tell me what this means in your query:?
> WITH ( [KEY] char(7), value char(1) '.' )
> I understand that the [KEY] and value are columns of char(7) and char(
1) ,
> but what does the '.' mean?.
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then ho
w
> would I identify each row uniquely?. The input XML has to come with some s
ort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> Thanks.
>
>
> "John Bell" wrote:
>|||John,
Thanks again, Could you also see the second part of the question.
*************
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some
sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
************
Thanks.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> '.' is an abbreviation for self::node() Check out "Specifying a Node Test
in
> the Location Path" in Books online.
> I could have written:
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), v
alue
> char(1) 'self::node()' )
> ) A
>
> John
> "Ganesh Muthuvelu" wrote:
>|||Hi
Your XML does not lend itself to pivoting if you have multiple response
segments. You would probably need to load it into a temporary table where yo
u
can create an artificial key and then generate the pivot from that.
Alternatively you may be able to use a transform and the position function t
o
achieve something similar. If you pivoted on the client you would not need
this extra step.
If you had multiple response segments, you would need a new root node.
HTH
John
"Ganesh Muthuvelu" wrote:
[vbcol=seagreen]
> John,
> Thanks again, Could you also see the second part of the question.
> *************
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then ho
w
> would I identify each row uniquely?. The input XML has to come with some
> sort
> of primary keys then?. As you may see, the MAX would work if there is onl
y
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> ************
> Thanks.
> "John Bell" wrote:
>

OPENXML Question

Hello,
This is my XML data:
<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>
How would I use OPENXML to get the data in this format:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
-- -- -- -- --
a B C d
Thanks.Hi
Try something like:
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
N'<RESPONSE>
<FIELDS>
<FIELD KEY="COLUMN1">a</FIELD>
<FIELD KEY="COLUMN2">B</FIELD>
<FIELD KEY="COLUMN3">C</FIELD>
<FIELD KEY="COLUMN4">d</FIELD>
</FIELDS>
</RESPONSE>'
-- Use OPENXML to provide rowset consisting of customer data.
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
) A
EXEC sp_xml_removedocument @.hdoc
The subquery and max functions are because you want to pivot the output. If
you can work with
SELECT [KEY], [value]
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) '.' )
it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
transformation.
John
"Ganesh Muthuvelu" wrote:
> Hello,
> This is my XML data:
> <RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>
> How would I use OPENXML to get the data in this format:
> COLUMN1 COLUMN2 COLUMN3 COLUMN4
> -- -- -- -- --
> a B C d
> Thanks.|||John,
Thanks for the reply. Could you tell me what this means in your query:?
WITH ( [KEY] char(7), value char(1) '.' )
I understand that the [KEY] and value are columns of char(7) and char(1) ,
but what does the '.' mean?.
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
Thanks.
"John Bell" wrote:
> Hi
> Try something like:
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> N'<RESPONSE>
> <FIELDS>
> <FIELD KEY="COLUMN1">a</FIELD>
> <FIELD KEY="COLUMN2">B</FIELD>
> <FIELD KEY="COLUMN3">C</FIELD>
> <FIELD KEY="COLUMN4">d</FIELD>
> </FIELDS>
> </RESPONSE>'
> -- Use OPENXML to provide rowset consisting of customer data.
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> ) A
> EXEC sp_xml_removedocument @.hdoc
> The subquery and max functions are because you want to pivot the output. If
> you can work with
> SELECT [KEY], [value]
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) '.' )
> it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> transformation.
> John
> "Ganesh Muthuvelu" wrote:
> > Hello,
> > This is my XML data:
> > <RESPONSE>
> > <FIELDS>
> > <FIELD KEY="COLUMN1">a</FIELD>
> > <FIELD KEY="COLUMN2">B</FIELD>
> > <FIELD KEY="COLUMN3">C</FIELD>
> > <FIELD KEY="COLUMN4">d</FIELD>
> > </FIELDS>
> > </RESPONSE>
> >
> > How would I use OPENXML to get the data in this format:
> > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > -- -- -- -- --
> > a B C d
> >
> > Thanks.|||Hi
'.' is an abbreviation for self::node() Check out "Specifying a Node Test in
the Location Path" in Books online.
I could have written:
SELECT MAX(COLUMN1) AS COLUMN1,
MAX(COLUMN2) AS COLUMN2,
MAX(COLUMN3) AS COLUMN3,
MAX(COLUMN4) AS COLUMN4
FROM (
SELECT [KEY],
CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
char(1) 'self::node()' )
) A
John
"Ganesh Muthuvelu" wrote:
> John,
> Thanks for the reply. Could you tell me what this means in your query:?
> WITH ( [KEY] char(7), value char(1) '.' )
> I understand that the [KEY] and value are columns of char(7) and char(1) ,
> but what does the '.' mean?.
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> Thanks.
>
>
> "John Bell" wrote:
> > Hi
> >
> > Try something like:
> >
> > DECLARE @.hDoc int
> > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > N'<RESPONSE>
> > <FIELDS>
> > <FIELD KEY="COLUMN1">a</FIELD>
> > <FIELD KEY="COLUMN2">B</FIELD>
> > <FIELD KEY="COLUMN3">C</FIELD>
> > <FIELD KEY="COLUMN4">d</FIELD>
> > </FIELDS>
> > </RESPONSE>'
> > -- Use OPENXML to provide rowset consisting of customer data.
> > SELECT MAX(COLUMN1) AS COLUMN1,
> > MAX(COLUMN2) AS COLUMN2,
> > MAX(COLUMN3) AS COLUMN3,
> > MAX(COLUMN4) AS COLUMN4
> > FROM (
> > SELECT [KEY],
> > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) '.' )
> > ) A
> >
> > EXEC sp_xml_removedocument @.hdoc
> >
> > The subquery and max functions are because you want to pivot the output. If
> > you can work with
> >
> > SELECT [KEY], [value]
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) '.' )
> >
> > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > transformation.
> >
> > John
> >
> > "Ganesh Muthuvelu" wrote:
> >
> > > Hello,
> > > This is my XML data:
> > > <RESPONSE>
> > > <FIELDS>
> > > <FIELD KEY="COLUMN1">a</FIELD>
> > > <FIELD KEY="COLUMN2">B</FIELD>
> > > <FIELD KEY="COLUMN3">C</FIELD>
> > > <FIELD KEY="COLUMN4">d</FIELD>
> > > </FIELDS>
> > > </RESPONSE>
> > >
> > > How would I use OPENXML to get the data in this format:
> > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > -- -- -- -- --
> > > a B C d
> > >
> > > Thanks.|||John,
Thanks again, Could you also see the second part of the question.
*************
Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
would I identify each row uniquely?. The input XML has to come with some
sort
of primary keys then?. As you may see, the MAX would work if there is only
one row - if there are mulitple rows and the input XML does not have any
"primary keys", how would I make this work?.
************
Thanks.
"John Bell" wrote:
> Hi
> '.' is an abbreviation for self::node() Check out "Specifying a Node Test in
> the Location Path" in Books online.
> I could have written:
> SELECT MAX(COLUMN1) AS COLUMN1,
> MAX(COLUMN2) AS COLUMN2,
> MAX(COLUMN3) AS COLUMN3,
> MAX(COLUMN4) AS COLUMN4
> FROM (
> SELECT [KEY],
> CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> char(1) 'self::node()' )
> ) A
>
> John
> "Ganesh Muthuvelu" wrote:
> > John,
> > Thanks for the reply. Could you tell me what this means in your query:?
> >
> > WITH ( [KEY] char(7), value char(1) '.' )
> >
> > I understand that the [KEY] and value are columns of char(7) and char(1) ,
> > but what does the '.' mean?.
> >
> > Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> > would I identify each row uniquely?. The input XML has to come with some sort
> > of primary keys then?. As you may see, the MAX would work if there is only
> > one row - if there are mulitple rows and the input XML does not have any
> > "primary keys", how would I make this work?.
> >
> > Thanks.
> >
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Try something like:
> > >
> > > DECLARE @.hDoc int
> > > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > > N'<RESPONSE>
> > > <FIELDS>
> > > <FIELD KEY="COLUMN1">a</FIELD>
> > > <FIELD KEY="COLUMN2">B</FIELD>
> > > <FIELD KEY="COLUMN3">C</FIELD>
> > > <FIELD KEY="COLUMN4">d</FIELD>
> > > </FIELDS>
> > > </RESPONSE>'
> > > -- Use OPENXML to provide rowset consisting of customer data.
> > > SELECT MAX(COLUMN1) AS COLUMN1,
> > > MAX(COLUMN2) AS COLUMN2,
> > > MAX(COLUMN3) AS COLUMN3,
> > > MAX(COLUMN4) AS COLUMN4
> > > FROM (
> > > SELECT [KEY],
> > > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > char(1) '.' )
> > > ) A
> > >
> > > EXEC sp_xml_removedocument @.hdoc
> > >
> > > The subquery and max functions are because you want to pivot the output. If
> > > you can work with
> > >
> > > SELECT [KEY], [value]
> > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > char(1) '.' )
> > >
> > > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > > transformation.
> > >
> > > John
> > >
> > > "Ganesh Muthuvelu" wrote:
> > >
> > > > Hello,
> > > > This is my XML data:
> > > > <RESPONSE>
> > > > <FIELDS>
> > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > </FIELDS>
> > > > </RESPONSE>
> > > >
> > > > How would I use OPENXML to get the data in this format:
> > > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > > -- -- -- -- --
> > > > a B C d
> > > >
> > > > Thanks.|||Hi
Your XML does not lend itself to pivoting if you have multiple response
segments. You would probably need to load it into a temporary table where you
can create an artificial key and then generate the pivot from that.
Alternatively you may be able to use a transform and the position function to
achieve something similar. If you pivoted on the client you would not need
this extra step.
If you had multiple response segments, you would need a new root node.
HTH
John
"Ganesh Muthuvelu" wrote:
> John,
> Thanks again, Could you also see the second part of the question.
> *************
> Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> would I identify each row uniquely?. The input XML has to come with some
> sort
> of primary keys then?. As you may see, the MAX would work if there is only
> one row - if there are mulitple rows and the input XML does not have any
> "primary keys", how would I make this work?.
> ************
> Thanks.
> "John Bell" wrote:
> > Hi
> >
> > '.' is an abbreviation for self::node() Check out "Specifying a Node Test in
> > the Location Path" in Books online.
> >
> > I could have written:
> >
> > SELECT MAX(COLUMN1) AS COLUMN1,
> > MAX(COLUMN2) AS COLUMN2,
> > MAX(COLUMN3) AS COLUMN3,
> > MAX(COLUMN4) AS COLUMN4
> > FROM (
> > SELECT [KEY],
> > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > char(1) 'self::node()' )
> > ) A
> >
> >
> > John
> >
> > "Ganesh Muthuvelu" wrote:
> >
> > > John,
> > > Thanks for the reply. Could you tell me what this means in your query:?
> > >
> > > WITH ( [KEY] char(7), value char(1) '.' )
> > >
> > > I understand that the [KEY] and value are columns of char(7) and char(1) ,
> > > but what does the '.' mean?.
> > >
> > > Also, if there are mutiple rows (meaning multiple <RESPONSE> tags) then how
> > > would I identify each row uniquely?. The input XML has to come with some sort
> > > of primary keys then?. As you may see, the MAX would work if there is only
> > > one row - if there are mulitple rows and the input XML does not have any
> > > "primary keys", how would I make this work?.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Try something like:
> > > >
> > > > DECLARE @.hDoc int
> > > > EXEC sp_xml_preparedocument @.hDoc OUTPUT,
> > > > N'<RESPONSE>
> > > > <FIELDS>
> > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > </FIELDS>
> > > > </RESPONSE>'
> > > > -- Use OPENXML to provide rowset consisting of customer data.
> > > > SELECT MAX(COLUMN1) AS COLUMN1,
> > > > MAX(COLUMN2) AS COLUMN2,
> > > > MAX(COLUMN3) AS COLUMN3,
> > > > MAX(COLUMN4) AS COLUMN4
> > > > FROM (
> > > > SELECT [KEY],
> > > > CASE WHEN [KEY]='COLUMN1' THEN value END AS COLUMN1,
> > > > CASE WHEN [KEY]='COLUMN2' THEN value END AS COLUMN2,
> > > > CASE WHEN [KEY]='COLUMN3' THEN value END AS COLUMN3,
> > > > CASE WHEN [KEY]='COLUMN4' THEN value END AS COLUMN4
> > > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > > char(1) '.' )
> > > > ) A
> > > >
> > > > EXEC sp_xml_removedocument @.hdoc
> > > >
> > > > The subquery and max functions are because you want to pivot the output. If
> > > > you can work with
> > > >
> > > > SELECT [KEY], [value]
> > > > FROM OPENXML(@.hDoc, N'/RESPONSE/FIELDS/FIELD') WITH ( [KEY] char(7), value
> > > > char(1) '.' )
> > > >
> > > > it would be better e.g. pivot on the client. SQL 2005 has a PIVOT
> > > > transformation.
> > > >
> > > > John
> > > >
> > > > "Ganesh Muthuvelu" wrote:
> > > >
> > > > > Hello,
> > > > > This is my XML data:
> > > > > <RESPONSE>
> > > > > <FIELDS>
> > > > > <FIELD KEY="COLUMN1">a</FIELD>
> > > > > <FIELD KEY="COLUMN2">B</FIELD>
> > > > > <FIELD KEY="COLUMN3">C</FIELD>
> > > > > <FIELD KEY="COLUMN4">d</FIELD>
> > > > > </FIELDS>
> > > > > </RESPONSE>
> > > > >
> > > > > How would I use OPENXML to get the data in this format:
> > > > > COLUMN1 COLUMN2 COLUMN3 COLUMN4
> > > > > -- -- -- -- --
> > > > > a B C d
> > > > >
> > > > > Thanks.