Failed to enable constraints. One or more rows contain values violating non-null, unique, or for

Hello all, I just want to make a quick post on the “Failed

to enable constraints. One or more rows contain values violating non-null,

unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a

CODES table with columns CODE and DESC. When I first set up the database I set CODE

to varchar(MAX) and same with DESC, Then when I put all the codes and

descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out

that it was the Colum DESC in the DataSet that was causing the problem, if you

enter your dataset designer and select one of your columns, Right click, properties,

there is a field called MAXLENGTH, When I checked it was set to 50 even though I

had set varchar(MAX), so I knew that some of my descriptions where bigger than

50 so I upped it to 100 and everything was working fine. I don’t know if this

will be applicable to everyone or just me for being stupid but hey, it worked

;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

Failed to enable constraints. One or more rows contain values violating non-null, unique, or for

Hello all, I just want to make a quick post on the “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a CODES table with columns CODE and DESC. When I first set up the database I set CODE to varchar(MAX) and same with DESC, Then when I put all the codes and descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out that it was the Colum DESC in the DataSet that was causing the problem, if you enter your dataset designer and select one of your columns, Right click, properties, there is a field called MAXLENGTH, When I checked it was set to 50 even though I had set varchar(MAX), so I knew that some of my descriptions where bigger than 50 so I upped it to 100 and everything was working fine. I don’t know if this will be applicable to everyone or just me for being stupid but hey, it worked ;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

|||hello I'm new using c# I would like to know how to put the minoccur property to 0. Im having the same error
thanks

Failed to enable constraints. One or more rows contain values violating non-null, unique, or

Hello all, I just want to make a quick post on the “Failed

to enable constraints. One or more rows contain values violating non-null,

unique, or foreign-key constraints.”

I am working on a small system at the moment and I have a

CODES table with columns CODE and DESC. When I first set up the database I set CODE

to varchar(MAX) and same with DESC, Then when I put all the codes and

descriptions in and tried to run fill I got the error.

Now weather this will help anyone I don’t know, I found out

that it was the Colum DESC in the DataSet that was causing the problem, if you

enter your dataset designer and select one of your columns, Right click, properties,

there is a field called MAXLENGTH, When I checked it was set to 50 even though I

had set varchar(MAX), so I knew that some of my descriptions where bigger than

50 so I upped it to 100 and everything was working fine. I don’t know if this

will be applicable to everyone or just me for being stupid but hey, it worked

;)

Moved to SQL server - seems off topic for the VS debugger.|||

I got the same error message because my dataset had columns with minoccur property as null but there were no records in the database. Some of these columns were nullable in the database but not marked as 0 in dataset minoccur property.

Solution - For every column thats marked as nullable in database, I set the minoccur property to 0. This makes dataser expect null values

failed insert because of duplicate rows and still locks held by application

Hi,
we are using SQL Server 2005.
We have a problem because of an application which tries to
insert a duplicate row in a table with primary key. This insert fails
of course but the locks are not released within this transcation.

This is not locking up on MS SQL Server 2000. But we can see the lock is still applied there.

Can someone help me on this?

Thanks,

Hmmm. What locks are held by the INSERT? Is this a stored procedure you are calling?

Can you post the code you are calling? It sounds like you might be in a Serializable isolation level, perhaps, and an explicit transaction is being started but not cleared. Are you using connection pooling?

|||We are not using connection pooling. The application is written in 'C' langugage and we are using Standard ODBC drivers provided by Microsoft. There are no explicit locking done for this insert query. After failure if I commit or rollback the lock gets released. I can see locks are also held in MS SQL Server 2000 also but they are not blocking other users to accress that perticular table. I have not chnaged isolation level in both (MS SQL Server 2005 and MS SQL Server 2000). The same application also runs with Oracle 9i and on failure of insert statment dos not locking oracle table. I am trying to figure out what locking mechanisum is changed in MS SQL Server 2005. Do I need to change any default settings in MS SQL Server 2005 which will not lock the table on failure of insert because of duplicate rows.|||

Try looking at the output of this query:

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,

request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then

(select object_name(object_id) from sys.partitions

where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

and this:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

to see if you have a statement executing and

See what kinds of locks are being held and what the isolation level is. It is probably just not being disconnected and has a transaction open. Then try profiler to see exactly what is being sent. There is likely a setting that you need to set, but without seeing the code this is not easy to do.

|||

Thanks for reply.

The first query is giving follwing result:

Login Tran_Isolation re_ses resource_type mode type Status Owner Table

sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 RID X LOCK GRANT TRANSACTION 72057594243186688
sasi ReadCommitted 56 PAGE IX LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 KEY X LOCK GRANT TRANSACTION ACLS6999
sasi ReadCommitted 56 OBJECT IX LOCK GRANT TRANSACTION ACLS6999

The second query is not returning any rows.

|||I think you are going to have to use profiler and trace the activity on your server for this process. Post the queries if you can and then we can see what might be wrong.

Fact Dimension

I have what I think is probably not a large fact table (approx 20 mil rows) of invoice line items. There are approx 4 mil uniquie invoiceID's listed in the fact table. I seem to have trouble getting the invoiceID to return as a dimension. I've tried creating a dimension based on the fact table and using this as a dimension. I've also tried pulling the invoiceID out in to a separate table in the dw, and creating a dimension off of it.

What happens is, I'll use some filter (typically based on date and ship location) to try and limit the number of invoices in play. Then, when I try to add the InvoiceID dimension the query usually fails due to lack of memory.

I should note, I use the cube browser for testing as well as some client tools like Tableau, Excel, and Proclarity. My users won't know MDX, so I hope a solution can be done in AS.

Sam

Hello! I think the way to solve this problem is to build a separate invoice number dimension and to create artifical levels in this dimension.

The first level can be the first character/number in the invoice number. On the second level you can use the two first characters/positions and so on.

With this technique you will avoid the problem of having ten thousand invoice number members(or more) being send down to the client.

It is the same as a cascading parameter in Reporting Services. Forze the user to only select a small number of the dimension members.

I ProClarity Professional there is a good search capacity that is seldom used. You will see it in the dimension tool.

HTH

Thomas Ivarsson

eXtremely Long Time in Execution Query

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

Reminds me of some hotfix descriptions - Have you checked knowledgebase articles for "slow query"? Here's an example that relates to fast forward-only queries:

"FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005": http://support.microsoft.com/default.aspx/kb/926024

|||

I found the reason, but not sure why, either do I have a way to fix that.

In one of my select sentence, I have a field, nvarchar(2000), if I exclude that field in the select statement then everything is back to speed. Otherwise, we are cralwing like 1000 lines per minute, so 1 million lines will be 1000 minute ... (without that field we are at 100,000 per minute). I am not sure why a field can cause such a big deal 100 times in performance diff ...

Microsoft has to explain this ...

Thanks,

Ning

|||

I am wondering if it is joining to the table containing this field early in the query and so having to carry the up to 2K a row of data round (for a million rows - that's up to 2G). This would probably show as an explosion in the size of tempdb (as this is where it is likely to be caching this data during the query).

Was it just the field or the table providing the field which you removed from the query. If it was the table you might try putting it at the bottom of the from clause and using the FORCE ORDER Query hint. However this will prevent the query planner rearranging any of the tables so you might want to put them in the order specified by the plan for the fast query without that field in.

extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both table
s
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repea
t
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:

> what is the best way of extracting just the rows that existing in both tab
les
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then rep
eat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>

extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both tables
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repeat
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:
> what is the best way of extracting just the rows that existing in both tables
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then repeat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>

extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both tables
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repeat
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.
Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:

> what is the best way of extracting just the rows that existing in both tables
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then repeat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>

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 h
ow
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)
> Lets 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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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...
>
>|||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 multipl
e
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. Than
k
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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...[vbcol=seagreen]
> 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, lets 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:
>|||Or
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(5
0)')
FROM @.tAddress
UNION ALL
SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(5
0)')
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,
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...
>
>|||Check out the nodes method. It returns a table with one column of datatype X
ML. So if the XML
instance is a column inside a table, you use this with APPLY against the tab
le 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...[vbcol=seagreen]
> 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:
>|||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...[vbcol=seagreen]
> 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:
>

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.
> >>
> >>
>

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
WindowsVista1
WindowsHome2
WindowsXP3
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)
> Lets 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...
>
>
|||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...[vbcol=seagreen]
> 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, lets 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:
|||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,
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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>