Extracting SQL fields from XML Data stored in NText field

I have a interesting situation, where XML is stored inside of SQL NText
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"