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