Extracting Sql: regarding

Showing posts with label regarding. Show all posts

Thursday, March 29, 2012

Failed to open malformed assembly 'mscorlib'

Hi,

I was using one of features provided in Microsoft samples regarding StringUtilities.dll. It was running fine for couple of month. Suddenly I am getting the following error message when try to execute this command

Select * from dbo.Split('123,25')

Msg 6507, Level 16, State 2, Procedure Split, Line 1

Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008.

Please Guide

Thanks

SqlServer process ran out of memory (HRESULT 0x80070008). I will file a DCR to make the error message better.

Beysim.

Failed to open malformed assembly 'mscorlib'

Hi,

Select * from dbo.Split('123,25')

Msg 6507, Level 16, State 2, Procedure Split, Line 1

Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008.

Please Guide

Thanks

SqlServer process ran out of memory (HRESULT 0x80070008). I will file a DCR to make the error message better.

Beysim.

sql

Wednesday, March 7, 2012

Factorial Question

Hi, folks.
This might be irrelevant to the SQL, but I have a question regarding the factorial function.
n! = n(n-1)(n-2)(n-3)...(2)(1)
I would like to find the value of n when I know the value of n!.
Does anyone know how to do this?
TIA.Hi, folks. This might be irrelevant to the SQL, but I have a question regarding the factorial function.
n! = n(n-1)(n-2)(n-3)...(2)(1)
I would like to find the value of n when I know the value of n!.
Q1 Does anyone know how to do this?
TIA.

A1 All you need to do is loop through an iterative calculation (n times) you can implement as a stored procedure, a Sql Server 2k fn, or just run the tsql:

Declare
@.vn int,
@.vi int,
@.vFactorialResult BigInt

Select @.vn = 4
If @.vn < 21 And @.vn > 0
Begin
Select @.vi = 1, @.vFactorialResult = 1
-- Check term iteration (loop)
While @.vi <= @.vn
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
-- increment loop iteration counter
Select @.vi = @.vi + 1
End
Select @.vn As 'n:', @.vFactorialResult As 'FactorialResult:'
End
Else If @.vn >= 21
Begin
Select @.vn As 'n is too large:'
End
Else
Begin
Select @.vn As 'Error n is:'
End

-- As a Sql Server 2k fn:
Create Function dbo.fn_Factorial (@.pn int)
Returns BigInt
AS
Begin
Declare
@.vi int,
@.vFactorialResult BigInt

If @.pn > 21 Or @.pn < 0 Return -1
Select @.vi = 1, @.vFactorialResult = 1
-- Check term iteration (loop)
While @.vi <= @.pn
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
-- increment loop iteration counter
Select @.vi = @.vi + 1
End
Return @.vFactorialResult
End

-- To use fn_Factorial
Declare
@.vn int,
@.vFactorialResult BigInt
Select @.vn = 6
Select @.vFactorialResult = [master].[dbo].[fn_Factorial](@.vn)
Select @.vn As 'n:', @.vFactorialResult As 'FactorialResult:'|||Sorry, DBA.

I think my message was not clear.

I don't know the value of n.
I only have the value of n!

Is it possible to enter the value of n!, and get the value of n calculated as output?

TIA|||Originally posted by iamcrom
Sorry, DBA.

I think my message was not clear.

I don't know the value of n.
I only have the value of n!

Is it possible to enter the value of n!, and get the value of n calculated as output?

TIA

My mistake, it looked like a simple typographical error. For that you could implement some kind of factoring procedure or an estimation procedure.

For an estimation based proc it would simply start with a initial guess and converge until the result is either exactly matched e.g.(Factorial 7) or it is determined that the number cannot be a factorial product e.g.(6020). It should be relativly straight forward to implement, (you should have less than 21 possibilities to check), but more complex than the factorial. Finding an optimized algorithim could take a while (and some testing).|||Thank you, DBA.

The problem is that I will be choosing one number from 1 to 75, depending on the probability given. This is problematic because getting that probability relates to binomial coefficient, which in turn relates to factorial.

I will have probability as input, and spit the corresponding number (1 - 75).

My memory is hazy, but I remember seeing a formula to calculate n in high school. Does anyone remember that formula?

It is driving me crazy.|||If you may be certain the number is a factorial product, I think you could simply use a proc similar to the factorial (except altered to succesively examine the modulus result)?

In that case the last 0 modulo result sould be n (which you could check by running fn_Factorial (n)) For example:

Select 24%2, 24%3, 24%4, 24%5

The last 0 result is for 4;

The check 4! = 24

If the result doesn't check it wouldn't be a factorial product.|||Never mind that won't work.|||The following assumes that the value you submit is a valid factorial number - if this is not a guarantee then just do a > comparison and bail out once the value exceeds the supplied factorial.

Declare
@.vi int,
@.vFactorialResult float,
@.vControl float

select @.vControl = 'the n! value
select @.vFactorialResult = 1, @.vi = 1
while @.vi < 76
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
if @.vControl = @.vFactorialResult
begin
Select @.vi as 'n found', @.vFactorialResult as 'result', @.vControl as 'control'
break
end
-- increment loop iteration counter
Select @.vi = @.vi + 1
End|||RE: For an estimation based proc it would simply start with a initial guess and converge until the result is either exactly matched e.g.(Factorial 7) or it is determined that the number cannot be a factorial product e.g.(6020). It should be relativly straight forward to implement, (you should have less than 21 possibilities to check), but more complex than the factorial. Finding an optimized algorithim could take a while (and some testing).

The following should do it to the nearest non-overflow est. n! (I used bigint instead of floats, you could always rewrite it):

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.

|||

Tiago Rente wrote:

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 19, 2012

Extracting info regarding ETL's

Dear all,
I'm pursuing for a method/query or any kind of task being able to find all
those DTS which own Oracle connections inside. It's very hard open one and
one (500)!!
Does anyone ever experienced or face up such requirement?
Thanks for your time and any input will be welcomed,
EnricI don't think it's possible to display dependencies of a DTS Package as it's
application code rather than SQL objects.
You may be able to save the DTS Packages as a Visual Basic file and then
search within the file. I have never tried this though.
HTH. Ryan
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
> Dear all,
> I'm pursuing for a method/query or any kind of task being able to find all
> those DTS which own Oracle connections inside. It's very hard open one and
> one (500)!!
> Does anyone ever experienced or face up such requirement?
> Thanks for your time and any input will be welcomed,
> Enric|||hi again,
Well, in a fact it's possible instancing dtspkg.dll with VB
and navigate through a specific DTS and obtaining the value for the global
variables and so on... I am trying to avoid this way, it's useless.
Thanks anyway,
"Ryan" wrote:

> I don't think it's possible to display dependencies of a DTS Package as it
's
> application code rather than SQL objects.
> You may be able to save the DTS Packages as a Visual Basic file and then
> search within the file. I have never tried this though.
> --
> HTH. Ryan
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D0217DB6-3099-4664-ABE5-BE383B825C60@.microsoft.com...
>
>|||I hope that the next version avoid this kind of situations.
"Enric" wrote:
> hi again,
> Well, in a fact it's possible instancing dtspkg.dll with VB
> and navigate through a specific DTS and obtaining the value for the global
> variables and so on... I am trying to avoid this way, it's useless.
> Thanks anyway,
> "Ryan" wrote:
>

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Hi,
I have a question regarding extracting XML data as relational data. From
the looks of it looks like the OPENXML statement is not relevant since the
data I need to extract resides in XML columns (in a table).
So I have this query
SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
MyXmlCol â' is an XML column, and the WHERE clause assures me that I know how
to access this XML data (using xpath)
Letâ's say the data in XML column looks like this
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
(were in each row the value of the Product & Version nodes are different).
I want a table/rowset with two columns Product (xpath
/MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
I want the rowset/table to look like this:
Product Version
-- --
WindowsVista 1
WindowsHome 2
WindowsXP 3
How do I accomplish this ?
--
Asher.Asher
Take a look at this example to resturn Cities
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
'varchar(50)' )
FROM @.tAddress
"Asher F." <asherfoa@.community.nospam> wrote in message
news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> Hi,
> I have a question regarding extracting XML data as relational data. From
> the looks of it looks like the OPENXML statement is not relevant since the
> data I need to extract resides in XML columns (in a table).
> So I have this query
> SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> MyXmlCol ? is an XML column, and the WHERE clause assures me that I know
> how
> to access this XML data (using xpath)
> Let?s say the data in XML column looks like this
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> (were in each row the value of the Product & Version nodes are different).
> I want a table/rowset with two columns Product (xpath
> /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> I want the rowset/table to look like this:
> Product Version
> -- --
> WindowsVista 1
> WindowsHome 2
> WindowsXP 3
> How do I accomplish this ?
>
> --
> Asher.|||Hello Asher,
I understand that you'd like to insert a table from a xml type value. You
may want to use openxml to do the job:
CREATE TABLE MyStuff( product varchar(20), version int)
go
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Mystuff
SELECT *
FROM OPENXML(@.docHandle, N'/MyStuff', 2)
WITH (proudct varchar(20) '/MyStuff/Product',
version int '/MyStuff/Version')
EXEC sp_xml_removedocument @.docHandle
select * from mystuff
If you have any comments or feedback, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Uri,
Thanks for the quick response, I understand the code you suggested and it
works for me 80% of times. I would very much appreciate your help in this
follow-up.
To extend your example, letâ's say I have this:
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="222 Left"
City="New York"
State="NY"
ZipCode = "2222" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
Note that the XML in the first row has two AddressRecord elements.
How do I make my result return a rowset with 3 rows representing all the
values:
SanDiego
New York
Houston
Thanks in advance!
--
Asher.
"Uri Dimant" wrote:
> Asher
> Take a look at this example to resturn Cities
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> 'varchar(50)' )
> FROM @.tAddress
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> > Hi,
> > I have a question regarding extracting XML data as relational data. From
> > the looks of it looks like the OPENXML statement is not relevant since the
> > data I need to extract resides in XML columns (in a table).
> > So I have this query
> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I know
> > how
> > to access this XML data (using xpath)
> > Letâ's say the data in XML column looks like this
> > <MyStuff>
> > <Product>Windows Vista</Product>
> > <Version>1</Version>
> > </MyStuff>
> > (were in each row the value of the Product & Version nodes are different).
> > I want a table/rowset with two columns Product (xpath
> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> > I want the rowset/table to look like this:
> > Product Version
> > -- --
> > WindowsVista 1
> > WindowsHome 2
> > WindowsXP 3
> >
> > How do I accomplish this ?
> >
> >
> >
> > --
> > Asher.
>
>|||Hello Peter,
Thank you for your response.
What I am trying to do is to extract data from an XML column I have in one
of my tables. I am trying to make the data in the XML column appear as part
of the normal rowset.
I will run a SQL SELECT statement from my C# code expecting to receive the
data back in the form of a rowset (so I can read it as primitive types using
IDataReader).
Anyway, Uri's response works for me in most of the cases, it doesn't cover
(and I tried several things myself) the case where I need to extract multiple
values (i.e. rows) from a single XML document (stored in the xml column in a
given row in my table).
Thanks for the help
--
Asher.
"Peter Yang [MSFT]" wrote:
> Hello Asher,
> I understand that you'd like to insert a table from a xml type value. You
> may want to use openxml to do the job:
> CREATE TABLE MyStuff( product varchar(20), version int)
> go
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> '
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- Use OPENXML to provide rowset consisting of customer data.
> INSERT Mystuff
> SELECT *
> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> WITH (proudct varchar(20) '/MyStuff/Product',
> version int '/MyStuff/Version')
> EXEC sp_xml_removedocument @.docHandle
> select * from mystuff
>
> If you have any comments or feedback, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Asher
I see what you mean
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="111 Left"
City="New_York"
State="NY"
ZipCode = "3333" />
</AddressType> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
WITH (City varchar(10))
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let?s say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||Or
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
FROM @.tAddress
UNION ALL
SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
FROM @.tAddress
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> Asher
> I see what you mean
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="111 Left"
> City="New_York"
> State="NY"
> ZipCode = "3333" />
> </AddressType> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> WITH (City varchar(10))
>
>
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> Hi Uri,
>> Thanks for the quick response, I understand the code you suggested and it
>> works for me 80% of times. I would very much appreciate your help in this
>> follow-up.
>> To extend your example, let?s say I have this:
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> <AddressRecord StreetLine1="222 Left"
>> City="New York"
>> State="NY"
>> ZipCode = "2222" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> Note that the XML in the first row has two AddressRecord elements.
>> How do I make my result return a rowset with 3 rows representing all the
>> values:
>> SanDiego
>> New York
>> Houston
>> Thanks in advance!
>> --
>> Asher.
>>
>> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>
>|||Hi Uri,
The OPENXML is probably not the solution since I have the XML document
stored in a column.
As for your second solution, it looks like it is in the right direction,
however I don't see how I can scale it to the case I have N (or an unkown
number of) AddressRecord nodes in each XML document (i.e. one row's XML
column might contain zero AddressRecord elements and another row might
contain 10...)
How do I scale this solution ?
--
Asher.
"Uri Dimant" wrote:
> Or
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
> FROM @.tAddress
> UNION ALL
> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
> FROM @.tAddress
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> > Asher
> > I see what you mean
> >
> > DECLARE @.idoc int
> >
> > DECLARE @.doc varchar(1000)
> >
> > SET @.doc ='
> >
> > <AddressType name="Shipping">
> >
> > <AddressRecord StreetLine1="111 Main"
> >
> > City="Houston"
> >
> > State="TX"
> >
> > ZipCode = "11111" />
> >
> > <AddressRecord StreetLine1="111 Left"
> >
> > City="New_York"
> >
> > State="NY"
> >
> > ZipCode = "3333" />
> >
> > </AddressType> '
> >
> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> >
> > SELECT *
> >
> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> >
> > WITH (City varchar(10))
> >
> >
> >
> >
> >
> >
> >
> > "Asher F." <asherfoa@.community.nospam> wrote in message
> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> >> Hi Uri,
> >> Thanks for the quick response, I understand the code you suggested and it
> >> works for me 80% of times. I would very much appreciate your help in this
> >> follow-up.
> >>
> >> To extend your example, letâ's say I have this:
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >> <AddressRecord StreetLine1="222 Left"
> >>
> >> City="New York"
> >>
> >> State="NY"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> Note that the XML in the first row has two AddressRecord elements.
> >> How do I make my result return a rowset with 3 rows representing all the
> >> values:
> >> SanDiego
> >> New York
> >> Houston
> >>
> >> Thanks in advance!
> >> --
> >> Asher.
> >>
> >>
> >> "Uri Dimant" wrote:
> >>
> >> Asher
> >> Take a look at this example to resturn Cities
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> >> 'varchar(50)' )
> >>
> >> FROM @.tAddress
> >>
> >>
> >>
> >> "Asher F." <asherfoa@.community.nospam> wrote in message
> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> >> > Hi,
> >> > I have a question regarding extracting XML data as relational data.
> >> > From
> >> > the looks of it looks like the OPENXML statement is not relevant since
> >> > the
> >> > data I need to extract resides in XML columns (in a table).
> >> > So I have this query
> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> >> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I
> >> > know
> >> > how
> >> > to access this XML data (using xpath)
> >> > Letâ's say the data in XML column looks like this
> >> > <MyStuff>
> >> > <Product>Windows Vista</Product>
> >> > <Version>1</Version>
> >> > </MyStuff>
> >> > (were in each row the value of the Product & Version nodes are
> >> > different).
> >> > I want a table/rowset with two columns Product (xpath
> >> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> >> > I want the rowset/table to look like this:
> >> > Product Version
> >> > -- --
> >> > WindowsVista 1
> >> > WindowsHome 2
> >> > WindowsXP 3
> >> >
> >> > How do I accomplish this ?
> >> >
> >> >
> >> >
> >> > --
> >> > Asher.
> >>
> >>
> >>
> >
> >
>
>|||Check out the nodes method. It returns a table with one column of datatype XML. So if the XML
instance is a column inside a table, you use this with APPLY against the table where the column
exists. Example, assuming table is temp table instead of table variable:
SELECT nc.value('@.City[1]', 'varchar(39)')
FROM #tAddress
CROSS APPLY Address.nodes('/AddressType/AddressRecord') AS nt(nc)
---
Houston
New York
SanDiego
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let's say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data. From
>> > the looks of it looks like the OPENXML statement is not relevant since the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol - is an XML column, and the WHERE clause assures me that I know
>> > how
>> > to access this XML data (using xpath)
>> > Let's say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||See Tibor's reply , it is what you wanted
"Asher F." <asherfoa@.community.nospam> wrote in message
news:38DFF454-06D5-4E00-B623-F5FB216D87EC@.microsoft.com...
> Hi Uri,
> The OPENXML is probably not the solution since I have the XML document
> stored in a column.
> As for your second solution, it looks like it is in the right direction,
> however I don't see how I can scale it to the case I have N (or an unkown
> number of) AddressRecord nodes in each XML document (i.e. one row's XML
> column might contain zero AddressRecord elements and another row might
> contain 10...)
> How do I scale this solution ?
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Or
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)')
>> FROM @.tAddress
>> UNION ALL
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]',
>> 'varchar(50)')
>> FROM @.tAddress
>>
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
>> > Asher
>> > I see what you mean
>> >
>> > DECLARE @.idoc int
>> >
>> > DECLARE @.doc varchar(1000)
>> >
>> > SET @.doc ='
>> >
>> > <AddressType name="Shipping">
>> >
>> > <AddressRecord StreetLine1="111 Main"
>> >
>> > City="Houston"
>> >
>> > State="TX"
>> >
>> > ZipCode = "11111" />
>> >
>> > <AddressRecord StreetLine1="111 Left"
>> >
>> > City="New_York"
>> >
>> > State="NY"
>> >
>> > ZipCode = "3333" />
>> >
>> > </AddressType> '
>> >
>> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
>> >
>> > SELECT *
>> >
>> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
>> >
>> > WITH (City varchar(10))
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Asher F." <asherfoa@.community.nospam> wrote in message
>> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> >> Hi Uri,
>> >> Thanks for the quick response, I understand the code you suggested and
>> >> it
>> >> works for me 80% of times. I would very much appreciate your help in
>> >> this
>> >> follow-up.
>> >>
>> >> To extend your example, let?s say I have this:
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >> <AddressRecord StreetLine1="222 Left"
>> >>
>> >> City="New York"
>> >>
>> >> State="NY"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> Note that the XML in the first row has two AddressRecord elements.
>> >> How do I make my result return a rowset with 3 rows representing all
>> >> the
>> >> values:
>> >> SanDiego
>> >> New York
>> >> Houston
>> >>
>> >> Thanks in advance!
>> >> --
>> >> Asher.
>> >>
>> >>
>> >> "Uri Dimant" wrote:
>> >>
>> >> Asher
>> >> Take a look at this example to resturn Cities
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> >> 'varchar(50)' )
>> >>
>> >> FROM @.tAddress
>> >>
>> >>
>> >>
>> >> "Asher F." <asherfoa@.community.nospam> wrote in message
>> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> >> > Hi,
>> >> > I have a question regarding extracting XML data as relational data.
>> >> > From
>> >> > the looks of it looks like the OPENXML statement is not relevant
>> >> > since
>> >> > the
>> >> > data I need to extract resides in XML columns (in a table).
>> >> > So I have this query
>> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> >> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> >> > know
>> >> > how
>> >> > to access this XML data (using xpath)
>> >> > Let?s say the data in XML column looks like this
>> >> > <MyStuff>
>> >> > <Product>Windows Vista</Product>
>> >> > <Version>1</Version>
>> >> > </MyStuff>
>> >> > (were in each row the value of the Product & Version nodes are
>> >> > different).
>> >> > I want a table/rowset with two columns Product (xpath
>> >> > /MyStuff/Product/text()) and Version (xpath
>> >> > /MyStuff/Version/text())
>> >> > I want the rowset/table to look like this:
>> >> > Product Version
>> >> > -- --
>> >> > WindowsVista 1
>> >> > WindowsHome 2
>> >> > WindowsXP 3
>> >> >
>> >> > How do I accomplish this ?
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Asher.
>> >>
>> >>
>> >>
>> >
>> >
>>|||Did you look at my suggestion? It produces exactly the result you asked for...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Asher F." <asherfoa@.community.nospam> wrote in message
news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> Hello Peter,
> Thank you for your response.
> What I am trying to do is to extract data from an XML column I have in one
> of my tables. I am trying to make the data in the XML column appear as part
> of the normal rowset.
> I will run a SQL SELECT statement from my C# code expecting to receive the
> data back in the form of a rowset (so I can read it as primitive types using
> IDataReader).
> Anyway, Uri's response works for me in most of the cases, it doesn't cover
> (and I tried several things myself) the case where I need to extract multiple
> values (i.e. rows) from a single XML document (stored in the xml column in a
> given row in my table).
> Thanks for the help
> --
> Asher.
>
> "Peter Yang [MSFT]" wrote:
>> Hello Asher,
>> I understand that you'd like to insert a table from a xml type value. You
>> may want to use openxml to do the job:
>> CREATE TABLE MyStuff( product varchar(20), version int)
>> go
>> DECLARE @.docHandle int
>> declare @.xmlDocument xml
>> set @.xmlDocument = N'
>> <MyStuff>
>> <Product>Windows Vista</Product>
>> <Version>1</Version>
>> </MyStuff>
>> '
>> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
>> -- Use OPENXML to provide rowset consisting of customer data.
>> INSERT Mystuff
>> SELECT *
>> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
>> WITH (proudct varchar(20) '/MyStuff/Product',
>> version int '/MyStuff/Version')
>> EXEC sp_xml_removedocument @.docHandle
>> select * from mystuff
>>
>> If you have any comments or feedback, please feel free to let's know. Thank
>> you.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications
>> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Thank you Tibor, it looks like exactlly what I need.
I am now reading the relevant documentation about the functions you used so
I can get a deeper understanding.
Thank you very much for your help!
--
Asher.
"Tibor Karaszi" wrote:
> Did you look at my suggestion? It produces exactly the result you asked for...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> > Hello Peter,
> > Thank you for your response.
> > What I am trying to do is to extract data from an XML column I have in one
> > of my tables. I am trying to make the data in the XML column appear as part
> > of the normal rowset.
> > I will run a SQL SELECT statement from my C# code expecting to receive the
> > data back in the form of a rowset (so I can read it as primitive types using
> > IDataReader).
> > Anyway, Uri's response works for me in most of the cases, it doesn't cover
> > (and I tried several things myself) the case where I need to extract multiple
> > values (i.e. rows) from a single XML document (stored in the xml column in a
> > given row in my table).
> >
> > Thanks for the help
> >
> > --
> > Asher.
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello Asher,
> >>
> >> I understand that you'd like to insert a table from a xml type value. You
> >> may want to use openxml to do the job:
> >>
> >> CREATE TABLE MyStuff( product varchar(20), version int)
> >> go
> >>
> >> DECLARE @.docHandle int
> >> declare @.xmlDocument xml
> >> set @.xmlDocument = N'
> >> <MyStuff>
> >> <Product>Windows Vista</Product>
> >> <Version>1</Version>
> >> </MyStuff>
> >> '
> >> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> >> -- Use OPENXML to provide rowset consisting of customer data.
> >> INSERT Mystuff
> >> SELECT *
> >> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> >> WITH (proudct varchar(20) '/MyStuff/Product',
> >> version int '/MyStuff/Version')
> >>
> >> EXEC sp_xml_removedocument @.docHandle
> >>
> >> select * from mystuff
> >>
> >>
> >> If you have any comments or feedback, please feel free to let's know. Thank
> >> you.
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Community Support
> >> ==================================================> >> Get notification to my posts through email? Please refer to
> >> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> >> ications
> >> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> >> where an initial response from the community or a Microsoft Support
> >> Engineer within 1 business day is acceptable. Please note that each follow
> >> up response may take approximately 2 business days as the support
> >> professional working with you may need further investigation to reach the
> >> most efficient resolution. The offering is not appropriate for situations
> >> that require urgent, real-time or phone-based interactions or complex
> >> project analysis and dump analysis issues. Issues of this nature are best
> >> handled working with a dedicated Microsoft Support Engineer by contacting
> >> Microsoft Customer Support Services (CSS) at
> >> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
>

Thursday, March 29, 2012

Failed to open malformed assembly 'mscorlib'

Failed to open malformed assembly 'mscorlib'

Wednesday, March 7, 2012

Factorial Question

Fact Table SQL Query - adding a prefix to a value

Sunday, February 19, 2012

Extracting info regarding ETL's

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Extract values from XML column (rows) to a rowset

Extract values from XML column (rows) to a rowset

Extracting Sql

Blog Archive

About Me