I have criteria in my dataset that throws the above error. Iâ'm just trying
to get RS to understand the parsing out of a DateTime Field the values of a
month or year. I know the value I am comparing to is a number. Iâ've tried
the following different ways:
= YEAR(@.Date_Selection) â' 1
= DatePart(yy,@.Date_Selection) - 1
Additionally, Iâ've tried casting and converting back and forth from and to
VarChar and Int to no avail.
Any thoughts?
--
Marty ClineIt would be easier to help you if we could see the field types of your table
and the whole query that's included in your SSRS dataset (including the
parameter).
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>I have criteria in my dataset that throws the above error. Iâ'm just trying
> to get RS to understand the parsing out of a DateTime Field the values of
> a
> month or year. I know the value I am comparing to is a number. Iâ've tried
> the following different ways:
> = YEAR(@.Date_Selection) â' 1
> = DatePart(yy,@.Date_Selection) - 1
> Additionally, Iâ've tried casting and converting back and forth from and to
> VarChar and Int to no avail.
> Any thoughts?
> --
> Marty Cline|||Sure, butt here's the real "kicker".
This work perfectly in SQL server studio. Reporting services just has
issues with the datetime parameter.
Here's the sql:
DECLARE @.DATE_Selection AS DATETIME
SET @.DATE_Selection = '10/31/2007'
SELECT
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD =td.Curr_hold INNER JOIN
Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
(ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
(1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection) -
1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||nevermind. It just started working. I have no idea why.
--
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||Did you set the proper type for your parameter in SSRS? It defaults to
string the first time you create it, and if you modify it afterwards, it
tends to revert back to string. From your SQL code, it should be datetime.
If that's not the issue, you could try this:
LEFT(convert(varchar, @.DATE_Selection, 2), 2)
instead of this:
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
And so on for the rest of your fields. You never know...
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> Sure, butt here's the real "kicker".
> This work perfectly in SQL server studio. Reporting services just has
> issues with the datetime parameter.
> Here's the sql:
> DECLARE @.DATE_Selection AS DATETIME
> SET @.DATE_Selection = '10/31/2007'
> SELECT
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => td.Curr_hold INNER JOIN
> Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> (1 = 1) AND (ir.YEAR = DATEPART(yy,
> @.Date_Selection) -
> 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> <=> 20) AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> It would be easier to help you if we could see the field types of your
>> table
>> and the whole query that's included in your SSRS dataset (including the
>> parameter).
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >trying
>> > to get RS to understand the parsing out of a DateTime Field the values
>> > of
>> > a
>> > month or year. I know the value I am comparing to is a number. Iâ've
>> > tried
>> > the following different ways:
>> >
>> > = YEAR(@.Date_Selection) â' 1
>> > = DatePart(yy,@.Date_Selection) - 1
>> >
>> > Additionally, Iâ've tried casting and converting back and forth from and
>> > to
>> > VarChar and Int to no avail.
>> >
>> > Any thoughts?
>> > --
>> >
>> > Marty Cline
>>|||Thanks for your response. To answer your question, yes the parameter type
was set to DateTime.
I'm not sure why it was not working to begin with but it seems to be working
now. Probably something I did not know I did, but I'm not arguing. Thanks
for your help again!
--
Marty Cline
"Alain Quesnel" wrote:
> Did you set the proper type for your parameter in SSRS? It defaults to
> string the first time you create it, and if you modify it afterwards, it
> tends to revert back to string. From your SQL code, it should be datetime.
> If that's not the issue, you could try this:
> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
> instead of this:
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
> And so on for the rest of your fields. You never know...
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> > Sure, butt here's the real "kicker".
> >
> > This work perfectly in SQL server studio. Reporting services just has
> > issues with the datetime parameter.
> >
> > Here's the sql:
> >
> > DECLARE @.DATE_Selection AS DATETIME
> > SET @.DATE_Selection = '10/31/2007'
> >
> > SELECT
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> >
> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> >
> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => > td.Curr_hold INNER JOIN
> > Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> > AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
> > @.Date_Selection) -
> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> > <=> > 20) AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
> >
> >
> > --
> >
> > Marty Cline
> >
> >
> > "Alain Quesnel" wrote:
> >
> >> It would be easier to help you if we could see the field types of your
> >> table
> >> and the whole query that's included in your SSRS dataset (including the
> >> parameter).
> >>
> >>
> >>
> >> Alain Quesnel
> >> alainsansspam@.logiquel.com
> >>
> >> www.logiquel.com
> >>
> >>
> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >> >I have criteria in my dataset that throws the above error. Iâ'm just
> >> >trying
> >> > to get RS to understand the parsing out of a DateTime Field the values
> >> > of
> >> > a
> >> > month or year. I know the value I am comparing to is a number. Iâ've
> >> > tried
> >> > the following different ways:
> >> >
> >> > = YEAR(@.Date_Selection) â' 1
> >> > = DatePart(yy,@.Date_Selection) - 1
> >> >
> >> > Additionally, Iâ've tried casting and converting back and forth from and
> >> > to
> >> > VarChar and Int to no avail.
> >> >
> >> > Any thoughts?
> >> > --
> >> >
> >> > Marty Cline
> >>
> >>
>|||You're welcome.
Debugging a report in SSRS based on the error message on the Preview page
isn't exactly intuitive. Or at least not in the beginning when you're not
familiar with the environment.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:491E40DE-D931-4162-B5F5-C707533EAD34@.microsoft.com...
> Thanks for your response. To answer your question, yes the parameter type
> was set to DateTime.
> I'm not sure why it was not working to begin with but it seems to be
> working
> now. Probably something I did not know I did, but I'm not arguing.
> Thanks
> for your help again!
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> Did you set the proper type for your parameter in SSRS? It defaults to
>> string the first time you create it, and if you modify it afterwards, it
>> tends to revert back to string. From your SQL code, it should be
>> datetime.
>> If that's not the issue, you could try this:
>> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
>> instead of this:
>> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
>> And so on for the rest of your fields. You never know...
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
>> > Sure, butt here's the real "kicker".
>> >
>> > This work perfectly in SQL server studio. Reporting services just has
>> > issues with the datetime parameter.
>> >
>> > Here's the sql:
>> >
>> > DECLARE @.DATE_Selection AS DATETIME
>> > SET @.DATE_Selection = '10/31/2007'
>> >
>> > SELECT
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
>> >
>> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS
>> > ReportOrder,
>> >
>> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month,
>> > DATENAME(d,
>> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
>> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL,
>> > ir.IMP_10_CNT,
>> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
>> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
>> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
>> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
>> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD
>> > =>> > td.Curr_hold INNER JOIN
>> > Investments.CAM_SEC_DIM AS sd ON td.Cusip =>> > sd.CUSIP
>> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
>> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=>> > 20)
>> > AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > OR
>> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
>> > @.Date_Selection) -
>> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND
>> > (ir.IMPAIRMENT_LVL
>> > <=>> > 20) AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>> >
>> >
>> > --
>> >
>> > Marty Cline
>> >
>> >
>> > "Alain Quesnel" wrote:
>> >
>> >> It would be easier to help you if we could see the field types of your
>> >> table
>> >> and the whole query that's included in your SSRS dataset (including
>> >> the
>> >> parameter).
>> >>
>> >>
>> >>
>> >> Alain Quesnel
>> >> alainsansspam@.logiquel.com
>> >>
>> >> www.logiquel.com
>> >>
>> >>
>> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >> >trying
>> >> > to get RS to understand the parsing out of a DateTime Field the
>> >> > values
>> >> > of
>> >> > a
>> >> > month or year. I know the value I am comparing to is a number. Iâ've
>> >> > tried
>> >> > the following different ways:
>> >> >
>> >> > = YEAR(@.Date_Selection) â' 1
>> >> > = DatePart(yy,@.Date_Selection) - 1
>> >> >
>> >> > Additionally, Iâ've tried casting and converting back and forth from
>> >> > and
>> >> > to
>> >> > VarChar and Int to no avail.
>> >> >
>> >> > Any thoughts?
>> >> > --
>> >> >
>> >> > Marty Cline
>> >>
>> >>
>>
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Friday, March 23, 2012
Failed to convert parameter value from a String to a Guid?
Hi all,
I have setup my database that have 3 columns:
1. Primary Key
2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.
3. Another Foreign key with just an int.
I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:
SELECT aspnet_Users.UserId, t_music.MUSIC_TITLE
FROM t_user_viewed
JOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId
JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FK
WHERE aspnet_Users.UserId = @.UserId
Any help would be greatly appreciated,
Kenny.
You need to post definition for @.UserId parameter.
|||Can you please elaborates? I'm new to asp.net as well as ms-sql.
Thank you,
Kenny.
Sounds like you need to cast the string value to GUID on the @.UserId when you call the select function and pass the @.UserId value.
ctype(StringValueGUID, GUID)
Burl
|||Where do I cast the string value? In the DAL (dataset) or where I have my ObjectDataSource doing the select? Sorry for my lack of knowledge on asp.net and mssql.
Thanks,
Kenny.
|||How are you passing the GUID to the ObjectDataSource for the select? Wherever that value comes from, its in a string type and needs to be cast as a GUID type. Can you post some code?
Burl
|||I've created a Dataset and setup my table, then I've created a BLL that have this code to get the fields:
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}
In my ObjectDataSource, I chose the above method (GetSongsByUserPK) and have the parameter source as a session. Here is the code for the ObjectDataSource:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>
The SELECT query is the same as my first post... is there anything else I need to do?
Thank you very, very much.
Kenny.
Well,
I've made some minor adjustment to my code and it's working now, however, the SELECT statement that I wrote in the DataSet didn't return the proper table. Here are the changes:
1. In my DataSet, I have setup the table called t_user_viewed that will store the primary key of t_music database as foreign key, and the UserId from the aspet_users as another foreign key. Here is the select statement:
SELECT *
FROM t_music a
JOIN t_user_viewed b on a.MUSIC_PK = b.MUSIC_PK_FK
JOIN aspnet_Users c ON b.UserID = c.UserId
WHERE b.UserID = @.UserId
So when I run this code, and enter the UserId, it should return the information from table "t_music" (with all information such as song title, artist, etc...) instead.
2. This is the code for the SongsBLL.cs class, to get the above select statement:
private t_user_viewedTableAdapter _t_user_viewed = null;
protected t_user_viewedTableAdapter Adapter2
{
get
{
if (_t_user_viewed == null)
_t_user_viewed = new t_user_viewedTableAdapter();
return _t_user_viewed;
}
}
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}
3. Here is the ObjectDataSource that was generated by VS:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL" OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>
4. Here is the GridView that was generated automatically when I select the DataSourceID to the above ObjectDataSource:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="VIEWED_PK"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="VIEWED_PK" HeaderText="VIEWED_PK" InsertVisible="False"
ReadOnly="True" SortExpression="VIEWED_PK" />
<asp:BoundField DataField="MUSIC_PK_FK" HeaderText="MUSIC_PK_FK" SortExpression="MUSIC_PK_FK" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
</Columns>
</asp:GridView>
Apparently, the GridView & the ObjectDataSource only pick up the t_music_viewed table columns, instead of the "t_music" columns that I specified in my Select statement.
Any idea?
Thank you very much,
Kenny.
I've solved it by building my SELECT query in the "t_music" table instead.
THANK YOU!
Kenny.
I've solved it by building my SELECT query in the "t_music" table instead.
THANK YOU!
Kenny.
Friday, March 9, 2012
Fail to get Return_Value in stored procedure NullValueReference
I have a stored procedure that insert into a table and return a value indicating if the insert really occured.
If the a insert occurs then it returns the identity of that insert.
I have slimmed down my code here, so you can see.
My problem is that a NullValueReference exceptions occurs when trying to reference the returnvalue.
Is it not possible to get a return value along with a recordset ?
publicint RegisterCustomer(Customer customer)
{
SqlCommand myCommand =newSqlCommand("ins_RegistrerKunde", myConnection);
myCommand.CommandType =CommandType.StoredProcedure;
SqlParameter Epost =newSqlParameter("@.Epost",SqlDbType.VarChar, 25);
SqlParameter Passord =newSqlParameter("@.Passord",SqlDbType.VarChar, 25);
SqlParameter ReturnValue =newSqlParameter("@.RETURN_VALUE",SqlDbType.Int, 4);
ReturnValue.Direction =ParameterDirection.ReturnValue;
Epost.Value = customer.Email;
Passord.Value = customer.Password;
myCommand.Parameters.Add(ReturnValue);
myCommand.Parameters.Add(Epost);
myCommand.Parameters.Add(Passord);
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.SingleRow);
int isRegistered = (int)myCommand.Parameters["@.RETURN_VALUE"].Value;
myConnection.Close();
if (isRegistered == 1)
{
result.Read();
returnConvert.ToInt32(result["Kunde_ID"]);
result.Close();
}
returnConvert.ToInt32(isRegistered);
}
//Stored Procedure ins_RegistrerKunde
create procedure%PROC% (@.Epost varchar(25), @.Passord varchar(25))
as
begin
IF EXISTS( SELECT EpostFROM tbl_KundeWHERE Epost = @.Epost)
RETURN 0
ELSE
INSERT INTO tbl_Kunde( Epost, Passord)
VALUES (@.Epost, @.Passord)
SELECT @.@.IDENTITY AS Kunde_ID
RETURN 1
end
go
Also, instead of @.@.IDENTITY, use SCOPE_IDENTITY(), as this will returnthe the last Identity value in the current scope. SeeIdentity Crisis andUnderstanding Identity Columns for an explanation.|||
I see.
Thank you.![Embarrassed [:$]](http://pics.10026.com/?src=/emoticons/emotion-10.gif)
fail to change the value of parameter with operator 'range'
Hi all,
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@.salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
Any idea and suggestion will be appreciated!
Jone
Anybody has any idea?
Thanks,
Wednesday, March 7, 2012
Fact Table SQL Query - adding a prefix to a value
Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).
The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...
Is there a SQL function that allows me to
Example datasets:
Code Snippet
ConsNo DelNNo Canc Turnover Amount
001 200 0 1000 500
002 200 1 1000 500
I'd like to achieve this output by a query:
Code Snippet
ConsNo DelNNo Turnover Amount
001 200 1000 500
002 200 -1000 -500
Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...
Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?|||
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
|||
Tiago Rente wrote:
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!
Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.
|||Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor 
).
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
|||
Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...
|||Summing the case statements should do the trick.
Code Snippet
select ConsNo,
DelNNo,
Turnover = SUM(case
when Canc = 0
then Turnover
else Turnover * -1
end),
Amount = SUM(case
when Canc = 0
then Amount
else Amount * -1
end)
from <table name>
GROUP BY
ConsNo
, DelNo
Sunday, February 26, 2012
F I L T E R HELP!!! PLEASE!!!! THIS CAN'T BE THAT HARD
In SSRS 2005, how can I add this check (what do I have to put in the Expression, operator, and value in filters tab of table properties) to my table filter to ensure only customers that match one of the ORs are viewed in my report?
HERE IS A PRINT SCREEN OF THE FILTER TAB WHERE i WANT THIS ALL TO HAPPEN...PROPERTIES OF MY TABLE:
http:
essentially I want this check to filter records on my table...match to this criteria from my dataset:
(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value = '0000002' or
Fields!CustomerNumber.Value = '0000003' or
Fields!CustomerNumber.Value = '0000004' or
Fields!CustomerNumber.Value = '0000155' or
Fields!CustomerNumber.Value = '0000156' or
Fields!CustomerNumber.Value = '0000159' or
Fields!CustomerNumber.Value = '0000160' or
Fields!CustomerNumber.Value = '0000161' or
Fields!CustomerNumber.Value = '0000118' or
Fields!CustomerNumber.Value = '0000153' or
Fields!CustomerNumber.Value = '0000152' or
Fields!CustomerNumber.Value = '0000108' or
Fields!CustomerNumber.Value = '0000158' or
Fields!CustomerNumber.Value = '0000133')
OR
(Fields!Branch.Value <> '00002' and
Fields!CustomerNumber.Value = '0000053' or
Fields!CustomerNumber.Value = '0000058' or
Fields!CustomerNumber.Value = '0000072' or
Fields!CustomerNumber.Value = '0000073' or
Fields!CustomerNumber.Value = '0000079' or
Fields!CustomerNumber.Value = '0000080' or
Fields!CustomerNumber.Value = '0000143' or
Fields!CustomerNumber.Value = '0000146' or
Fields!CustomerNumber.Value = '0000157' or
Fields!CustomerNumber.Value = '0000135')
Fav, Why don't you put these values into a SQL DB table and add it as an additional dataset for your report. Then, just reference the new column/table in the Expression definition.
Just a thought .....
|||because while that would work, it's more overhead. I want to be able to use the reporting interface, that's what it's for. I'm not one to like to code all this crap in the backend for a report as complicated as the one I have. I'd like to be able to use the tool, SSRS which is what it's for and manage the filters, at least some of the major ones through the UI..I don't like managing everything in my stored procs and tables...that is way too much work...and that's why a reporting interface along with .NET and VB can help manage. I already have around 7 stored procs and 5 tables for this report...I don't need any more overhead....I don't want reporting to be 100% of my life at work...easier to maintain this with SSRS reporting filters. The problem is, SSRS 2005 is too new and nobody seems to know how to tell me how to form expressions in table filters at this point it looks like.
Why use reporting services if you can't harness filters like I want? think of it the reverse. If I did all this in SQL, first it would take me a year, second, then I would have no need for a UI...well, I don't have time to code everything in SQL, that's what UI is there to help with. I come from Crystal...if I had to code all that in SQL, adios to all my other projects. The stored procs behind my 5 datasets in this report have enough code in it....it's a nightmare.
|||u can try this one
(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value in ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )
something like this, u can put all these constant values in expression after selecting IN operator
|||thanks, so just so I have this right....in the filter screen, I'd put something like:
Expression Operator Value
Fields!CustomerNumber IN (Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value in ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )
I need to be sure I'm putting your expression in the right place and that my operator is correct. So would you expression go on the expression side or Value when you look at my URL above?
|||So, I tried this, but it doesn't like the syntax and how I set it up in this whole tab. Am I not doing this right?
http:
i think u have to use like this
Expression Operator Value
Fields!Branch.Value = '00002'
Fields!CustomerNumber.Value IN ( '0000002' ,'0000003' , '0000004', '0000155' , '0000133') )
use double quotes instead of single quotes if this does not work
OR else u can try this
Expression Operator Value
Fields!CustomerNumber.Value IN ( iif(Fields!Branch.Value = '00002' ,('0000002' ,'0000003' , '0000004', '0000155' , '0000133'), ('0000001'))
use iif function check this out in MSDN and try out
|||thanks, but what about the other OR piece?|||
i think u should use IIF for this - so for that OR piece u have to include all the ids where i have included ('000001')
that is, if use IIF function it needs three parameters, first parameter will be an expression, in ur case to check branch value is '000002',
second parameter will if the expression is true, include all the ids when branch value is '000002'
third parameter will be if the expression is false,include all the ids when branch value is not equal to '000002'
Sunday, February 19, 2012
Extracting the date from a datetime value
Hi,
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:
>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:
> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:
> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:
>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:
> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:
> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>
Extracting Integer
Hi,
I have on NCHAR column which has value like.. 1:25:234567. I have to
extract last part of value i. e. 234567 which comes after sign ':' .
The no. of digits vary. How can i do that using TSQL .
Please Help !!
Thanks
NJneeju a crit :
> Hi,
> I have on NCHAR column which has value like.. 1:25:234567. I have to
> extract last part of value i. e. 234567 which comes after sign ':' .
> The no. of digits vary. How can i do that using TSQL .
> Please Help !!
> Thanks
> NJ
>
SELECT REVERSE(SUBSTRING(REVERSE('1:25:234567')
, 1, CHARINDEX(':',
REVERSE('1:25:234567'))-1))
More generally :
SELECT REVERSE(SUBSTRING(REVERSE(MyColumn), 1, CHARINDEX(':',
REVERSE(MyColumn))-1))
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Oh Thanks a lot...that worked after small change..
Thanks again
NJ
I have on NCHAR column which has value like.. 1:25:234567. I have to
extract last part of value i. e. 234567 which comes after sign ':' .
The no. of digits vary. How can i do that using TSQL .
Please Help !!
Thanks
NJneeju a crit :
> Hi,
> I have on NCHAR column which has value like.. 1:25:234567. I have to
> extract last part of value i. e. 234567 which comes after sign ':' .
> The no. of digits vary. How can i do that using TSQL .
> Please Help !!
> Thanks
> NJ
>
SELECT REVERSE(SUBSTRING(REVERSE('1:25:234567')
, 1, CHARINDEX(':',
REVERSE('1:25:234567'))-1))
More generally :
SELECT REVERSE(SUBSTRING(REVERSE(MyColumn), 1, CHARINDEX(':',
REVERSE(MyColumn))-1))
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Oh Thanks a lot...that worked after small change..
Thanks again
NJ
Extracting href from XML using OpenXML
Hi,
Please look at the full XML that I have pasted below. Can
anybody tell me how can I extract the "href" value from the
following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
way I extract id from <multiref id="id2">.
Thanks for the help
Vinod
select ID,
pageId,
docName,
docType,
url,
href,
corr_group_seq,
corr_cat_seq,
corr_subject_seq,
attach_filename,
template_desc,
entity_type_seq,
entity_id,
template_seq,
case_type_seq,
attach_date_ts,
case_id,
case_name,
attach_by_user_seq,corr_seq from OPENXML('<?xml
version="1.0" encoding="UTF-8"?><soapenv:Envelope
><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns10:DocfinityDocument"
xmlns:ns10="http://document.docfinity.ms.acs.com";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<pageId xsi:type="soapenc:string">7</pageId>
<docName xsi:type="soapenc:string">3003</docName>
<docType xsi:type="soapenc:string">Image</docType>
<url
/0/7.tif<
/url>
<cascadeKeys href="http://links.10026.com/?link=#id26"/>
<userKeys href="http://links.10026.com/?link=#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns20:Map"
xmlns:ns20="http://xml.apache.org/xml-soap";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
><item><key
xsi:type="soapenc:string">29</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">39</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">49</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">24</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">50</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">23</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">34</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">52</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">30</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">18</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">20</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">42</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">25</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">32</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">43</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">35</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">13</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">26</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">44</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">9</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">19</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">51</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">28</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">3</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">41</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">27</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">14</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">17</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">6</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">1</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">36</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">5</key><value
xsi:type="soapenc:string">RRI</value></item>
<item><key xsi:type="soapenc:string">16</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">46</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">37</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">55</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">33</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">11</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">15</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">4</key>
<value
xsi:type="soapenc:string">07003830030000030</value></item><item>
<key
xsi:type="soapenc:string">40</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">12</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">45</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">7</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">2</key><value
xsi:type="soapenc:string">CMS1500</value></item><item><
key
xsi:type="soapenc:string">38</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">53</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">31</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">47</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">54</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">21</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">48</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">10</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">22</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">8</key>
<value
xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
</soapenv:Envelope>'
,'//multiRef') with
("ID" char(10) '@.id',
pageId char(20) 'pageId',
docName char(30) 'docName',
docType char(30) 'docType',
url varchar(500) 'url',
href varchar(20) '@.href',
corr_group_seq char(32) 'item/value[../key="4"]',
corr_cat_seq char(32) 'item/value[../key="20"]',
corr_subject_seq char(32) 'item/value[../key="5"]',
attach_filename char(32) 'item/value[../key="17"]',
template_desc char(32) 'item/value[../key="6"]',
entity_type_seq char(32) 'item/value[../key="5"]',
entity_id char(32) 'item/value[../key="4"]',
template_seq char(32) 'item/value[../key="4"]',
case_type_seq char(32) 'item/value[../key="16"]',
attach_date_ts char(32) 'item/value[../key="13"]',
case_id char(32) 'item/value[../key="9"]',
case_name char(32) 'item/value[../key="11"]',
attach_by_user_seq char(32) 'item/value[../key="15"]',
corr_seq char(32) 'item/value[../key="2"]')
Do you still need help with this?
Just a few tips:
1. You have to use sp_xml_preparedocument to parse the document (and use the
3rd parameter to provide namespace bindings) and then use the handle as
first parameter of OpenXML.
2. Do not forget to release the handle using sp_xml_removedocument
3. be sure that you find the performance of doing complex path expressions
in the row and column patterns. Otherwise use relational predicates instead.
Best regards
Michael
<vinodthomas@.hotmail.com> wrote in message
news:1174668434.957676.227050@.e65g2000hsc.googlegr oups.com...
> Hi,
> Please look at the full XML that I have pasted below. Can
> anybody tell me how can I extract the "href" value from the
> following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
> way I extract id from <multiref id="id2">.
> Thanks for the help
> Vinod
> select ID,
> pageId,
> docName,
> docType,
> url,
> href,
> corr_group_seq,
> corr_cat_seq,
> corr_subject_seq,
> attach_filename,
> template_desc,
> entity_type_seq,
> entity_id,
> template_seq,
> case_type_seq,
> attach_date_ts,
> case_id,
> case_name,
> attach_by_user_seq,corr_seq from OPENXML('<?xml
> version="1.0" encoding="UTF-8"?><soapenv:Envelope
> <ns1:getMatchingDocumentsResponse
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> <getMatchingDocumentsReturn
> soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
> </getMatchingDocumentsReturn>
> </ns1:getMatchingDocumentsResponse>
> <multiRef id="id2" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns10:DocfinityDocument"
> xmlns:ns10="http://document.docfinity.ms.acs.com";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <pageId xsi:type="soapenc:string">7</pageId>
> <docName xsi:type="soapenc:string">3003</docName>
> <docType xsi:type="soapenc:string">Image</docType>
> <url
> /0/7.tif<
> /url>
> <cascadeKeys href="http://links.10026.com/?link=#id26"/>
> <userKeys href="http://links.10026.com/?link=#id27"/>
> </multiRef>
> <multiRef id="id27" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns20:Map"
> xmlns:ns20="http://xml.apache.org/xml-soap";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="soapenc:string">29</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">39</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">49</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">24</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">50</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">23</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">34</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">52</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">30</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">18</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">20</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">42</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">25</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">32</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">43</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">35</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">13</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">26</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">44</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">9</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">19</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">51</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">28</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">3</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">41</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">27</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">14</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">17</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">6</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">1</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">36</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">5</key><value
> xsi:type="soapenc:string">RRI</value></item>
> <item><key xsi:type="soapenc:string">16</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">46</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">37</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">55</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">33</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">11</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">15</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">4</key>
> <value
> xsi:type="soapenc:string">07003830030000030</value></item><item>
> <key
> xsi:type="soapenc:string">40</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">12</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">45</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">7</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">2</key><value
> xsi:type="soapenc:string">CMS1500</value></item><item><
> key
> xsi:type="soapenc:string">38</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">53</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">31</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">47</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">54</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">21</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">48</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">10</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">22</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">8</key>
> <value
> xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
> </soapenv:Envelope>'
> ,'//multiRef') with
> ("ID" char(10) '@.id',
> pageId char(20) 'pageId',
> docName char(30) 'docName',
> docType char(30) 'docType',
> url varchar(500) 'url',
> href varchar(20) '@.href',
> corr_group_seq char(32) 'item/value[../key="4"]',
> corr_cat_seq char(32) 'item/value[../key="20"]',
> corr_subject_seq char(32) 'item/value[../key="5"]',
> attach_filename char(32) 'item/value[../key="17"]',
> template_desc char(32) 'item/value[../key="6"]',
> entity_type_seq char(32) 'item/value[../key="5"]',
> entity_id char(32) 'item/value[../key="4"]',
> template_seq char(32) 'item/value[../key="4"]',
> case_type_seq char(32) 'item/value[../key="16"]',
> attach_date_ts char(32) 'item/value[../key="13"]',
> case_id char(32) 'item/value[../key="9"]',
> case_name char(32) 'item/value[../key="11"]',
> attach_by_user_seq char(32) 'item/value[../key="15"]',
> corr_seq char(32) 'item/value[../key="2"]')
>
Please look at the full XML that I have pasted below. Can
anybody tell me how can I extract the "href" value from the
following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
way I extract id from <multiref id="id2">.
Thanks for the help
Vinod
select ID,
pageId,
docName,
docType,
url,
href,
corr_group_seq,
corr_cat_seq,
corr_subject_seq,
attach_filename,
template_desc,
entity_type_seq,
entity_id,
template_seq,
case_type_seq,
attach_date_ts,
case_id,
case_name,
attach_by_user_seq,corr_seq from OPENXML('<?xml
version="1.0" encoding="UTF-8"?><soapenv:Envelope
><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns10:DocfinityDocument"
xmlns:ns10="http://document.docfinity.ms.acs.com";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<pageId xsi:type="soapenc:string">7</pageId>
<docName xsi:type="soapenc:string">3003</docName>
<docType xsi:type="soapenc:string">Image</docType>
<url
/0/7.tif<
/url>
<cascadeKeys href="http://links.10026.com/?link=#id26"/>
<userKeys href="http://links.10026.com/?link=#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns20:Map"
xmlns:ns20="http://xml.apache.org/xml-soap";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
><item><key
xsi:type="soapenc:string">29</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">39</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">49</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">24</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">50</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">23</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">34</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">52</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">30</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">18</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">20</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">42</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">25</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">32</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">43</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">35</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">13</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">26</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">44</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">9</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">19</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">51</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">28</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">3</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">41</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">27</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">14</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">17</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">6</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">1</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">36</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">5</key><value
xsi:type="soapenc:string">RRI</value></item>
<item><key xsi:type="soapenc:string">16</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">46</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">37</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">55</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">33</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">11</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">15</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">4</key>
<value
xsi:type="soapenc:string">07003830030000030</value></item><item>
<key
xsi:type="soapenc:string">40</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">12</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">45</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">7</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">2</key><value
xsi:type="soapenc:string">CMS1500</value></item><item><
key
xsi:type="soapenc:string">38</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">53</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">31</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">47</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">54</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">21</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">48</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">10</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">22</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">8</key>
<value
xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
</soapenv:Envelope>'
,'//multiRef') with
("ID" char(10) '@.id',
pageId char(20) 'pageId',
docName char(30) 'docName',
docType char(30) 'docType',
url varchar(500) 'url',
href varchar(20) '@.href',
corr_group_seq char(32) 'item/value[../key="4"]',
corr_cat_seq char(32) 'item/value[../key="20"]',
corr_subject_seq char(32) 'item/value[../key="5"]',
attach_filename char(32) 'item/value[../key="17"]',
template_desc char(32) 'item/value[../key="6"]',
entity_type_seq char(32) 'item/value[../key="5"]',
entity_id char(32) 'item/value[../key="4"]',
template_seq char(32) 'item/value[../key="4"]',
case_type_seq char(32) 'item/value[../key="16"]',
attach_date_ts char(32) 'item/value[../key="13"]',
case_id char(32) 'item/value[../key="9"]',
case_name char(32) 'item/value[../key="11"]',
attach_by_user_seq char(32) 'item/value[../key="15"]',
corr_seq char(32) 'item/value[../key="2"]')
Do you still need help with this?
Just a few tips:
1. You have to use sp_xml_preparedocument to parse the document (and use the
3rd parameter to provide namespace bindings) and then use the handle as
first parameter of OpenXML.
2. Do not forget to release the handle using sp_xml_removedocument
3. be sure that you find the performance of doing complex path expressions
in the row and column patterns. Otherwise use relational predicates instead.
Best regards
Michael
<vinodthomas@.hotmail.com> wrote in message
news:1174668434.957676.227050@.e65g2000hsc.googlegr oups.com...
> Hi,
> Please look at the full XML that I have pasted below. Can
> anybody tell me how can I extract the "href" value from the
> following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
> way I extract id from <multiref id="id2">.
> Thanks for the help
> Vinod
> select ID,
> pageId,
> docName,
> docType,
> url,
> href,
> corr_group_seq,
> corr_cat_seq,
> corr_subject_seq,
> attach_filename,
> template_desc,
> entity_type_seq,
> entity_id,
> template_seq,
> case_type_seq,
> attach_date_ts,
> case_id,
> case_name,
> attach_by_user_seq,corr_seq from OPENXML('<?xml
> version="1.0" encoding="UTF-8"?><soapenv:Envelope
> <ns1:getMatchingDocumentsResponse
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> <getMatchingDocumentsReturn
> soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
> </getMatchingDocumentsReturn>
> </ns1:getMatchingDocumentsResponse>
> <multiRef id="id2" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns10:DocfinityDocument"
> xmlns:ns10="http://document.docfinity.ms.acs.com";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <pageId xsi:type="soapenc:string">7</pageId>
> <docName xsi:type="soapenc:string">3003</docName>
> <docType xsi:type="soapenc:string">Image</docType>
> <url
> /0/7.tif<
> /url>
> <cascadeKeys href="http://links.10026.com/?link=#id26"/>
> <userKeys href="http://links.10026.com/?link=#id27"/>
> </multiRef>
> <multiRef id="id27" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns20:Map"
> xmlns:ns20="http://xml.apache.org/xml-soap";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="soapenc:string">29</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">39</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">49</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">24</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">50</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">23</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">34</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">52</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">30</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">18</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">20</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">42</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">25</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">32</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">43</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">35</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">13</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">26</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">44</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">9</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">19</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">51</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">28</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">3</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">41</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">27</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">14</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">17</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">6</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">1</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">36</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">5</key><value
> xsi:type="soapenc:string">RRI</value></item>
> <item><key xsi:type="soapenc:string">16</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">46</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">37</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">55</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">33</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">11</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">15</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">4</key>
> <value
> xsi:type="soapenc:string">07003830030000030</value></item><item>
> <key
> xsi:type="soapenc:string">40</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">12</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">45</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">7</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">2</key><value
> xsi:type="soapenc:string">CMS1500</value></item><item><
> key
> xsi:type="soapenc:string">38</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">53</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">31</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">47</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">54</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">21</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">48</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">10</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">22</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">8</key>
> <value
> xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
> </soapenv:Envelope>'
> ,'//multiRef') with
> ("ID" char(10) '@.id',
> pageId char(20) 'pageId',
> docName char(30) 'docName',
> docType char(30) 'docType',
> url varchar(500) 'url',
> href varchar(20) '@.href',
> corr_group_seq char(32) 'item/value[../key="4"]',
> corr_cat_seq char(32) 'item/value[../key="20"]',
> corr_subject_seq char(32) 'item/value[../key="5"]',
> attach_filename char(32) 'item/value[../key="17"]',
> template_desc char(32) 'item/value[../key="6"]',
> entity_type_seq char(32) 'item/value[../key="5"]',
> entity_id char(32) 'item/value[../key="4"]',
> template_seq char(32) 'item/value[../key="4"]',
> case_type_seq char(32) 'item/value[../key="16"]',
> attach_date_ts char(32) 'item/value[../key="13"]',
> case_id char(32) 'item/value[../key="9"]',
> case_name char(32) 'item/value[../key="11"]',
> attach_by_user_seq char(32) 'item/value[../key="15"]',
> corr_seq char(32) 'item/value[../key="2"]')
>
Extracting href from XML using OpenXML
Hi,
Please look at the full XML that I have pasted below. Can
anybody tell me how can I extract the "href" value from the
following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
way I extract id from <multiref id="id2">.
Thanks for the help
Vinod
select ID,
pageId,
docName,
docType,
url,
href,
corr_group_seq,
corr_cat_seq,
corr_subject_seq,
attach_filename,
template_desc,
entity_type_seq,
entity_id,
template_seq,
case_type_seq,
attach_date_ts,
case_id,
case_name,
attach_by_user_seq,corr_seq from OPENXML('<?xml
version="1.0" encoding="UTF-8"?><soapenv:Envelope
><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns10:DocfinityDocument"
xmlns:ns10="http://document.docfinity.ms.acs.com";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<pageId xsi:type="soapenc:string">7</pageId>
<docName xsi:type="soapenc:string">3003</docName>
<docType xsi:type="soapenc:string">Image</docType>
<url
/0/7.tif<
/url>
<cascadeKeys href="http://links.10026.com/?link=#id26"/>
<userKeys href="http://links.10026.com/?link=#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns20:Map"
xmlns:ns20="http://xml.apache.org/xml-soap";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
><item><key
xsi:type="soapenc:string">29</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">39</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">49</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">24</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">50</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">23</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">34</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">52</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">30</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">18</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">20</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">42</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">25</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">32</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">43</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">35</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">13</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">26</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">44</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">9</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">19</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">51</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">28</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">3</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">41</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">27</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">14</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">17</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">6</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">1</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">36</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">5</key><value
xsi:type="soapenc:string">RRI</value></item>
<item><key xsi:type="soapenc:string">16</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">46</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">37</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">55</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">33</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">11</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">15</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">4</key>
<value
xsi:type="soapenc:string">07003830030000030</value></item><item>
<key
xsi:type="soapenc:string">40</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">12</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">45</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">7</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">2</key><value
xsi:type="soapenc:string">CMS1500</value></item><item><
key
xsi:type="soapenc:string">38</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">53</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">31</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">47</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">54</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">21</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">48</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">10</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">22</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">8</key>
<value
xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
</soapenv:Envelope>'
,'//multiRef') with
("ID" char(10) '@.id',
pageId char(20) 'pageId',
docName char(30) 'docName',
docType char(30) 'docType',
url varchar(500) 'url',
href varchar(20) '@.href',
corr_group_seq char(32) 'item/value[../key="4"]',
corr_cat_seq char(32) 'item/value[../key="20"]',
corr_subject_seq char(32) 'item/value[../key="5"]',
attach_filename char(32) 'item/value[../key="17"]',
template_desc char(32) 'item/value[../key="6"]',
entity_type_seq char(32) 'item/value[../key="5"]',
entity_id char(32) 'item/value[../key="4"]',
template_seq char(32) 'item/value[../key="4"]',
case_type_seq char(32) 'item/value[../key="16"]',
attach_date_ts char(32) 'item/value[../key="13"]',
case_id char(32) 'item/value[../key="9"]',
case_name char(32) 'item/value[../key="11"]',
attach_by_user_seq char(32) 'item/value[../key="15"]',
corr_seq char(32) 'item/value[../key="2"]')Do you still need help with this?
Just a few tips:
1. You have to use sp_xml_preparedocument to parse the document (and use the
3rd parameter to provide namespace bindings) and then use the handle as
first parameter of OpenXML.
2. Do not forget to release the handle using sp_xml_removedocument
3. be sure that you find the performance of doing complex path expressions
in the row and column patterns. Otherwise use relational predicates instead.
Best regards
Michael
<vinodthomas@.hotmail.com> wrote in message
news:1174668434.957676.227050@.e65g2000hsc.googlegroups.com...
> Hi,
> Please look at the full XML that I have pasted below. Can
> anybody tell me how can I extract the "href" value from the
> following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
> way I extract id from <multiref id="id2">.
> Thanks for the help
> Vinod
> select ID,
> pageId,
> docName,
> docType,
> url,
> href,
> corr_group_seq,
> corr_cat_seq,
> corr_subject_seq,
> attach_filename,
> template_desc,
> entity_type_seq,
> entity_id,
> template_seq,
> case_type_seq,
> attach_date_ts,
> case_id,
> case_name,
> attach_by_user_seq,corr_seq from OPENXML('<?xml
> version="1.0" encoding="UTF-8"?><soapenv:Envelope
> <ns1:getMatchingDocumentsResponse
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> <getMatchingDocumentsReturn
> soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
> </getMatchingDocumentsReturn>
> </ns1:getMatchingDocumentsResponse>
> <multiRef id="id2" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns10:DocfinityDocument"
> xmlns:ns10="http://document.docfinity.ms.acs.com";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <pageId xsi:type="soapenc:string">7</pageId>
> <docName xsi:type="soapenc:string">3003</docName>
> <docType xsi:type="soapenc:string">Image</docType>
> <url
> /0/7.tif<
> /url>
> <cascadeKeys href="http://links.10026.com/?link=#id26"/>
> <userKeys href="http://links.10026.com/?link=#id27"/>
> </multiRef>
> <multiRef id="id27" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns20:Map"
> xmlns:ns20="http://xml.apache.org/xml-soap";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="soapenc:string">29</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">39</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">49</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">24</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">50</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">23</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">34</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">52</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">30</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">18</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">20</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">42</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">25</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">32</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">43</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">35</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">13</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">26</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">44</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">9</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">19</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">51</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">28</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">3</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">41</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">27</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">14</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">17</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">6</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">1</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">36</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">5</key><value
> xsi:type="soapenc:string">RRI</value></item>
> <item><key xsi:type="soapenc:string">16</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">46</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">37</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">55</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">33</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">11</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">15</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">4</key>
> <value
> xsi:type="soapenc:string">07003830030000030</value></item><item>
> <key
> xsi:type="soapenc:string">40</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">12</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">45</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">7</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">2</key><value
> xsi:type="soapenc:string">CMS1500</value></item><item><
> key
> xsi:type="soapenc:string">38</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">53</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">31</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">47</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">54</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">21</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">48</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">10</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">22</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">8</key>
> <value
> xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
> </soapenv:Envelope>'
> ,'//multiRef') with
> ("ID" char(10) '@.id',
> pageId char(20) 'pageId',
> docName char(30) 'docName',
> docType char(30) 'docType',
> url varchar(500) 'url',
> href varchar(20) '@.href',
> corr_group_seq char(32) 'item/value[../key="4"]',
> corr_cat_seq char(32) 'item/value[../key="20"]',
> corr_subject_seq char(32) 'item/value[../key="5"]',
> attach_filename char(32) 'item/value[../key="17"]',
> template_desc char(32) 'item/value[../key="6"]',
> entity_type_seq char(32) 'item/value[../key="5"]',
> entity_id char(32) 'item/value[../key="4"]',
> template_seq char(32) 'item/value[../key="4"]',
> case_type_seq char(32) 'item/value[../key="16"]',
> attach_date_ts char(32) 'item/value[../key="13"]',
> case_id char(32) 'item/value[../key="9"]',
> case_name char(32) 'item/value[../key="11"]',
> attach_by_user_seq char(32) 'item/value[../key="15"]',
> corr_seq char(32) 'item/value[../key="2"]')
>
Please look at the full XML that I have pasted below. Can
anybody tell me how can I extract the "href" value from the
following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
way I extract id from <multiref id="id2">.
Thanks for the help
Vinod
select ID,
pageId,
docName,
docType,
url,
href,
corr_group_seq,
corr_cat_seq,
corr_subject_seq,
attach_filename,
template_desc,
entity_type_seq,
entity_id,
template_seq,
case_type_seq,
attach_date_ts,
case_id,
case_name,
attach_by_user_seq,corr_seq from OPENXML('<?xml
version="1.0" encoding="UTF-8"?><soapenv:Envelope
><soapenv:Body>
<ns1:getMatchingDocumentsResponse
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
<getMatchingDocumentsReturn
soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
</getMatchingDocumentsReturn>
</ns1:getMatchingDocumentsResponse>
<multiRef id="id2" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns10:DocfinityDocument"
xmlns:ns10="http://document.docfinity.ms.acs.com";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
<pageId xsi:type="soapenc:string">7</pageId>
<docName xsi:type="soapenc:string">3003</docName>
<docType xsi:type="soapenc:string">Image</docType>
<url
/0/7.tif<
/url>
<cascadeKeys href="http://links.10026.com/?link=#id26"/>
<userKeys href="http://links.10026.com/?link=#id27"/>
</multiRef>
<multiRef id="id27" soapenc:root="0"
soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
xsi:type="ns20:Map"
xmlns:ns20="http://xml.apache.org/xml-soap";
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
><item><key
xsi:type="soapenc:string">29</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">39</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">49</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">24</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">50</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">23</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">34</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">52</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">30</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">18</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">20</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">42</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">25</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">32</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">43</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">35</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">13</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">26</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">44</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">9</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">19</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">51</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">28</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">3</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">41</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">27</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">14</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">17</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">6</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">1</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">36</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">5</key><value
xsi:type="soapenc:string">RRI</value></item>
<item><key xsi:type="soapenc:string">16</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">46</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">37</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">55</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">33</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">11</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">15</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">4</key>
<value
xsi:type="soapenc:string">07003830030000030</value></item><item>
<key
xsi:type="soapenc:string">40</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">12</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">45</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">7</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">2</key><value
xsi:type="soapenc:string">CMS1500</value></item><item><
key
xsi:type="soapenc:string">38</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">53</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">31</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">47</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">54</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">21</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">48</key>
<value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">10</key><value
xsi:type="soapenc:string"></value></item>
<item><key xsi:type="soapenc:string">22</key><value
xsi:type="soapenc:string"></value></item><item><key
xsi:type="soapenc:string">8</key>
<value
xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
</soapenv:Envelope>'
,'//multiRef') with
("ID" char(10) '@.id',
pageId char(20) 'pageId',
docName char(30) 'docName',
docType char(30) 'docType',
url varchar(500) 'url',
href varchar(20) '@.href',
corr_group_seq char(32) 'item/value[../key="4"]',
corr_cat_seq char(32) 'item/value[../key="20"]',
corr_subject_seq char(32) 'item/value[../key="5"]',
attach_filename char(32) 'item/value[../key="17"]',
template_desc char(32) 'item/value[../key="6"]',
entity_type_seq char(32) 'item/value[../key="5"]',
entity_id char(32) 'item/value[../key="4"]',
template_seq char(32) 'item/value[../key="4"]',
case_type_seq char(32) 'item/value[../key="16"]',
attach_date_ts char(32) 'item/value[../key="13"]',
case_id char(32) 'item/value[../key="9"]',
case_name char(32) 'item/value[../key="11"]',
attach_by_user_seq char(32) 'item/value[../key="15"]',
corr_seq char(32) 'item/value[../key="2"]')Do you still need help with this?
Just a few tips:
1. You have to use sp_xml_preparedocument to parse the document (and use the
3rd parameter to provide namespace bindings) and then use the handle as
first parameter of OpenXML.
2. Do not forget to release the handle using sp_xml_removedocument
3. be sure that you find the performance of doing complex path expressions
in the row and column patterns. Otherwise use relational predicates instead.
Best regards
Michael
<vinodthomas@.hotmail.com> wrote in message
news:1174668434.957676.227050@.e65g2000hsc.googlegroups.com...
> Hi,
> Please look at the full XML that I have pasted below. Can
> anybody tell me how can I extract the "href" value from the
> following userKeys tag "<userKeys href="http://links.10026.com/?link=#id27"/>" like the
> way I extract id from <multiref id="id2">.
> Thanks for the help
> Vinod
> select ID,
> pageId,
> docName,
> docType,
> url,
> href,
> corr_group_seq,
> corr_cat_seq,
> corr_subject_seq,
> attach_filename,
> template_desc,
> entity_type_seq,
> entity_id,
> template_seq,
> case_type_seq,
> attach_date_ts,
> case_id,
> case_name,
> attach_by_user_seq,corr_seq from OPENXML('<?xml
> version="1.0" encoding="UTF-8"?><soapenv:Envelope
> <ns1:getMatchingDocumentsResponse
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> <getMatchingDocumentsReturn
> soapenc:arrayType="xsd:anyType[10]" xsi:type="soapenc:Array"
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <getMatchingDocumentsReturn href="http://links.10026.com/?link=#id0"/>
> </getMatchingDocumentsReturn>
> </ns1:getMatchingDocumentsResponse>
> <multiRef id="id2" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns10:DocfinityDocument"
> xmlns:ns10="http://document.docfinity.ms.acs.com";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";>
> <pageId xsi:type="soapenc:string">7</pageId>
> <docName xsi:type="soapenc:string">3003</docName>
> <docType xsi:type="soapenc:string">Image</docType>
> <url
> /0/7.tif<
> /url>
> <cascadeKeys href="http://links.10026.com/?link=#id26"/>
> <userKeys href="http://links.10026.com/?link=#id27"/>
> </multiRef>
> <multiRef id="id27" soapenc:root="0"
> soapenv:encodingxstyle="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="ns20:Map"
> xmlns:ns20="http://xml.apache.org/xml-soap";
> xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/";
> xsi:type="soapenc:string">29</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">39</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">49</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">24</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">50</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">23</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">34</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">52</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">30</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">18</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">20</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">42</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">25</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">32</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">43</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">35</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">13</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">26</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">44</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">9</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">19</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">51</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">28</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">3</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">41</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">27</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">14</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">17</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">6</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">1</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">36</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">5</key><value
> xsi:type="soapenc:string">RRI</value></item>
> <item><key xsi:type="soapenc:string">16</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">46</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">37</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">55</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">33</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">11</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">15</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">4</key>
> <value
> xsi:type="soapenc:string">07003830030000030</value></item><item>
> <key
> xsi:type="soapenc:string">40</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">12</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">45</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">7</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">2</key><value
> xsi:type="soapenc:string">CMS1500</value></item><item><
> key
> xsi:type="soapenc:string">38</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">53</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">31</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">47</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">54</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">21</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">48</key>
> <value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">10</key><value
> xsi:type="soapenc:string"></value></item>
> <item><key xsi:type="soapenc:string">22</key><value
> xsi:type="soapenc:string"></value></item><item><key
> xsi:type="soapenc:string">8</key>
> <value
> xsi:type="soapenc:string"></value></item></multiRef></soapenv:Body>
> </soapenv:Envelope>'
> ,'//multiRef') with
> ("ID" char(10) '@.id',
> pageId char(20) 'pageId',
> docName char(30) 'docName',
> docType char(30) 'docType',
> url varchar(500) 'url',
> href varchar(20) '@.href',
> corr_group_seq char(32) 'item/value[../key="4"]',
> corr_cat_seq char(32) 'item/value[../key="20"]',
> corr_subject_seq char(32) 'item/value[../key="5"]',
> attach_filename char(32) 'item/value[../key="17"]',
> template_desc char(32) 'item/value[../key="6"]',
> entity_type_seq char(32) 'item/value[../key="5"]',
> entity_id char(32) 'item/value[../key="4"]',
> template_seq char(32) 'item/value[../key="4"]',
> case_type_seq char(32) 'item/value[../key="16"]',
> attach_date_ts char(32) 'item/value[../key="13"]',
> case_id char(32) 'item/value[../key="9"]',
> case_name char(32) 'item/value[../key="11"]',
> attach_by_user_seq char(32) 'item/value[../key="15"]',
> corr_seq char(32) 'item/value[../key="2"]')
>
Subscribe to:
Posts (Atom)