• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Need SQL query help - SQL Server


-- 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
milani_lucie
Asked:
milani_lucie
  • 2
1 Solution
 
Faiga DiegelCommented:
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
 
milani_lucieAuthor Commented:
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
 
milani_lucieAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now