Showing posts with label relevant. Show all posts
Showing posts with label relevant. Show all posts

Friday, March 23, 2012

Failed to enumerate changes in the filtered articles. Incorrect syntax near '-'.

Thanks Kestutis,
the only hotfix I'm aware of that might be relevant is
http://support.microsoft.com/default.aspx?scid=kb;en-
us;810072
There is a completish list at
http://www.aspfaq.com/show.asp?id=2160 so one could wade
through it to see if there is anything relevant, but the
format of the list means that this'll take ages -
definitely one for Hilary's secret contact.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I think description of the problem and MS supported fix for it can be found
at http://support.microsoft.com/default...b;en-us;814916 but as I
mentioned you can get this fix only by contacting PSS. And also I was unable
to find "a success story" on the web from someone who applied this fix... I
was only able to find a short message from someone, that he applied the fix
and replication was working afterwards - no details, whatsoever..
Thanks for the link to SQL Server versions list, that's a really good
collection.
Would also be interesting to know which of those fixes do changes on
"database side" - I mean not fixing some executables or dll's, but making
changes to system SP's or table schema's (the area, where we can also make
some modifications). But I'm afraid we have to gather such information by
ourselves.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:236001c4dd48$1e32b540$a301280a@.phx.gbl...
> Thanks Kestutis,
> the only hotfix I'm aware of that might be relevant is
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;810072
> There is a completish list at
> http://www.aspfaq.com/show.asp?id=2160 so one could wade
> through it to see if there is anything relevant, but the
> format of the list means that this'll take ages -
> definitely one for Hilary's secret contact.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

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