Solved

Need SQL query help - SQL Server

Posted on 2010-08-13
3
259 Views
Last Modified: 2012-05-10

-- Declare XML string variable


DECLARE @XMLString varchar(5000)


-- Set value to the XML string variable


SET @XMLString ='
<ROOT>
  <row au_id="172-32-1176"
       au_lname="White"
       au_fname="Johnson"
       phone="408 496-7223"
       address="10932 Bigge Rd."
       city="Menlo Park"
       state="CA"
       zip="94025"
       contract="1">
  </row>
  <row au_id="213-46-8915"
       au_lname="Green"
       au_fname="Marjorie"
       phone="415 986-7020"
       address="309 63rd St. #411"
       city="Oakland"
       state="CA"
       zip="94618"
       contract="1">
  </row>
</ROOT>'


-- NODES


SELECT       c.value('@au_lname', 'varchar(25)'),

      c.value('@au_fname', 'varchar(25)')

FROM @XMLString.nodes('/ROOT/row') T(c)

This gives an error. Can you please provide me necessary SQL statement by fixing this ?

Thanks
0
Comment
Question by:milani_lucie
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
faiga16 earned 500 total points
ID: 33434534
Use something like this:

drop table #myXML
go
create table #myXML (id int identity(1,1) primary key,
        xm_data xml)
go
insert into #myXML (xm_data)
select
'<ROOT>
  <row au_id="172-32-1176"
       au_lname="White"
       au_fname="Johnson"
       phone="408 496-7223"
       address="10932 Bigge Rd."
       city="Menlo Park"
       state="CA"
       zip="94025"
       contract="1">
  </row>
  <row au_id="213-46-8915"
       au_lname="Green"
       au_fname="Marjorie"
       phone="415 986-7020"
       address="309 63rd St. #411"
       city="Oakland"
       state="CA"
       zip="94618"
       contract="1">
  </row>
</ROOT>'

go
SELECT       c.value('@au_lname', 'varchar(25)'),

      c.value('@au_fname', 'varchar(25)')
from #myXML cross apply xm_data.nodes('/ROOT/row') T(c)
go

The nodes should be an XML data type.

I got the same idea on this site: http://www.sqlservercentral.com/Forums/Topic732029-21-1.aspx
0
 

Author Comment

by:milani_lucie
ID: 33434565
Sorry .... Here is the query:

DECLARE @XMLString XML

-- Set value to the XML string variable

SET @XMLString ='
<ROOT>
<row>
<au_id>172-32-1176</au_id>
<au_lname>White</au_lname>
<au_fname>Johnson</au_fname>
<phone>408 496-7223</phone>
<address>10932 Bigge Rd.</address>
<city>Menlo Park</city>
<state>CA</state>
<zip>94025</zip>
<contract>1</contract>
</row>
<row>
<au_id>213-46-8915</au_id>
<au_lname>Green</au_lname>
<au_fname>Marjorie</au_fname>
<phone>415 986-7020</phone>
<address>309 63rd St. #411</address>
<city>Oakland</city>
<state>CA</state>
<zip>94618</zip>
<contract>1</contract>
</row>
</ROOT>'

-- NODES

SELECT c.value('au_lname', 'varchar(25)') au_lname,
c.value('au_fname', 'varchar(25)') au_fname
FROM @XMLString.nodes('/ROOT/row') T(c)
Thanks
0
 

Author Comment

by:milani_lucie
ID: 33434570
This works good if the datatype is XML. Here you go:
-- Declare XML string variable


DECLARE @XMLString XML

-- Set value to the XML string variable


SET @XMLString ='
<ROOT>
  <row au_id="172-32-1176"
       au_lname="White"
       au_fname="Johnson"
       phone="408 496-7223"
       address="10932 Bigge Rd."
       city="Menlo Park"
       state="CA"
       zip="94025"
       contract="1">
  </row>
  <row au_id="213-46-8915"
       au_lname="Green"
       au_fname="Marjorie"
       phone="415 986-7020"
       address="309 63rd St. #411"
       city="Oakland"
       state="CA"
       zip="94618"
       contract="1">
  </row>
</ROOT>'


-- NODES


SELECT       c.value('@au_lname', 'varchar(25)'),

      c.value('@au_fname', 'varchar(25)')

FROM @XMLString.nodes('/ROOT/row') T(c)
 
 Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now