Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Extract Data from XML using SQL server 2005

Posted on 2009-07-09
11
Medium Priority
?
74 Views
Last Modified: 2012-06-10
Having difficulty to extract data from XML document I am using .value method but it extracts only a single piece of information from XML document.
How do I extract all the values from xml? Any help would be much appreciated...
DECLARE @XmlDocument xml
 
SET @XmlDocument = N'<report>
  <d-report>
    <summary>
      <d-nbr>200700606058</d-nbr>
      <d-ver>17</d-ver>      
    </summary>
  </d-report>
  <d-report>
    <summary>
      <d-nbr>200600925766</d-nbr>
      <d-ver>10</d-ver>      
    </summary>
  </d-report>
  <d-report>
    <summary>
      <d-nbr>200600917443</d-nbr>
      <d-ver>13</d-ver>
    </summary>
  </d-report>
  <d-report>
    <summary>
      <d-nbr>200600913074</d-nbr>
      <d-ver>30</d-ver>
    </summary>
  </d-report>  
</report>'
 
SELECT @XmlDocument.value('(/report/d-report/summary/d-nbr)[1]', 'nvarchar(50)' ) [d-nbr],
@XmlDocument.value('(/report/d-report/summary/d-ver)[1]', 'nvarchar(50)' ) [d-ver]

Open in new window

0
Comment
Question by:andrishelp
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24818483
SELECT ParamValues.ID.value('d-nbr[1]','VARCHAR(20)') [d-nbr],
       ParamValues.ID.value('d-ver[1]','VARCHAR(20)')[d-ver]
FROM @XmlDocument.nodes('/report/d-report/summary') as ParamValues(ID)
0
 

Author Comment

by:andrishelp
ID: 24823276
Thanks for your quick response. I have changed few things in the XML Document. Now I am not getting correct result set. Please check attach file. Thanks.
XML-SQL.doc
0
 
LVL 4

Expert Comment

by:pepepaco
ID: 24824133
change your select query for

SELECT cust.coldef.value('(d-nbr)[1]', 'nvarchar(50)' ) [d-nbr],
cust.coldef.value('(d-ver)[1]', 'nvarchar(50)' ) [d-ver]
from @strXML.nodes('/report/d-report/summary') AS cust(coldef)


regards.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 4

Expert Comment

by:pepepaco
ID: 24824155
i forgot to mention i changed the xml variable from @XmlDocument  to @strXML please take into account.
you can switch the name back if you want.
regards.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 24824193
Since you do not have a primary key, the best you can do is:

SELECT      [d-nbr],
      MIN([d-ver]) [d-ver],
      MIN([p-title]) [p-title]
FROM      (
    SELECT  ParamValues.ID.value('d-nbr[1]', 'VARCHAR(20)') [d-nbr],
          ParamValues.ID.value('d-ver[1]', 'VARCHAR(20)') [d-ver],
          project.ID.value('title[1]', 'nvarchar(500)') [p-title]
    FROM    @XmlDocument.nodes('/report/d-report/summary') ParamValues (ID),
          @XmlDocument.nodes('/report/d-report/data/proj-title/title-code') project (ID)) d
GROUP BY [d-nbr]
0
 

Author Comment

by:andrishelp
ID: 24824240
After modifying xml file, getting duplicate records. Could you please review the attach file that I have sent eariler? Thanks.
0
 
LVL 4

Accepted Solution

by:
pepepaco earned 1400 total points
ID: 24824923
do this work for you??


SELECT ParamValues.ID.value('(summary/d-nbr)[1]','VARCHAR(20)') [d-nbr]
,ParamValues.ID.value('(summary/d-ver)[1]','VARCHAR(20)') [d-ver]
,ParamValues.ID.value('(data/proj-title/title-code/title)[1]','VARCHAR(20)') [title1]
,ParamValues.ID.value('(data/proj-title/title-code/title)[2]','VARCHAR(20)') [title2]
FROM @XmlDocument.nodes('/report/d-report') as ParamValues(ID)


it works only if there are always 2 titles at max...

regards
0
 
LVL 4

Expert Comment

by:pepepaco
ID: 24824983
other way to do it exactly as you need it is :

select * from (
SELECT ParamValues.ID.value('(summary/d-nbr)[1]','VARCHAR(20)') [d-nbr]
,ParamValues.ID.value('(summary/d-ver)[1]','VARCHAR(20)') [d-ver]
,ParamValues.ID.value('(data/proj-title/title-code/title)[1]','VARCHAR(20)') [title1]
FROM @XmlDocument.nodes('/report/d-report') as ParamValues(ID)
union all
SELECT ParamValues.ID.value('(summary/d-nbr)[1]','VARCHAR(20)') [d-nbr]
,ParamValues.ID.value('(summary/d-ver)[1]','VARCHAR(20)') [d-ver]
,ParamValues.ID.value('(data/proj-title/title-code/title)[2]','VARCHAR(20)') [title1]
FROM @XmlDocument.nodes('/report/d-report') as ParamValues(ID))sub
order by [d-nbr],[d-ver],[title1]


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24825306
I thought I answered the original question :(
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24825609
It is what you call a moving target...
0
 
LVL 4

Expert Comment

by:pepepaco
ID: 24825781
I think I answered without any assistance!
.
..
...
 I'm just kidding.  :)


0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

578 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