?
Solved

SQL, XML, Xpath, SQL Server 2005

Posted on 2008-11-05
3
Medium Priority
?
433 Views
Last Modified: 2008-11-15
I have created the following tables:

CREATE TABLE [Stg_Xml](
      [XmlFilename] [varchar](255) NOT NULL,
      [XmlData] [xml] NOT NULL
)

CREATE TABLE [Stg_Computer](
      [name] [varchar](255) NOT NULL,
      [ip] [varchar](255) NOT NULL
)

CREATE TABLE [Stg_Application](
      [name] [varchar](255) NOT NULL,
      [version] [varchar](255) NOT NULL
)

I have the following xml files:

file1.xml
<data>
      <computer>
            <name>Computer A</name>
            <ip>192.168.0.1</ip
            <applications>
                  <app name="Word" version="1.0"/>
                  <app name="Excel" version="3.1"/>
                  <app name="Access" version="0.2"/>
            </applications>
      </computer>
</data>

file2.xml
<data>
      <computer>
            <name>Computer B</name>
            <ip>192.168.0.2</ip
            <applications>
                  <app name="Paint" version="0.8"/>
                  <app name="Visio" version="0.4"/>
                  <app name="Notepad" version="5.3"/>
            </applications>
      </computer>
</data>

Xml files 1 and 2 get loaded into the Stg_Xml table with the following statements:

INSERT INTO Stg_Xml([XmlFilename], [XmlData])
      SELECT 'file1.xml', * FROM OPENROWSET(BULK 'C:\file1.xml', SINGLE_BLOB) AS x

INSERT INTO Stg_Xml([XmlFilename], [XmlData])
      SELECT 'file2.xml', * FROM OPENROWSET(BULK 'C:\file2.xml', SINGLE_BLOB) AS x

When I want to select all the computer data for insertion in the Stg_Computer table i can use the following statement:

SELECT
      XmlFilename
,      XMLData.value('(/data/computer/name)[1]', 'varchar(255)') AS [Name]
,      XMLData.value('(/data/computer/ip)[1]', 'varchar(255)') AS Ip
FROM
      Stg_Xml

This would create the following recordset:

Name            Ip
Computer A      192.168.0.1
Computer B      192.168.0.1

This is one query which gives me all the names and ips of all the xml files at once (set based). But I just do not know how to do this same thing with the applications. I want to create a recordset that has all the applications in it like this:

Name      Version
Word      1.0
Excel      3.1
Access      0.2
Paint      0.8
Vision      0.4
Notepad      5.3

The only way that I can think of is by using a cursor and looping through each of the files in the Stg_Xml table and  inserting the result of the following query in the Stg_App table:

SELECT      @XmlFilename AS XmlFilename
,            applications.app.value('./@name','varchar(100)') AS [Name]
,            applications.app.value('./@version','varchar(100)') AS Version
FROM      @XmlData.nodes('//applications/app') AS applications(app)


The variable @XmlFilename contains the xmlfilename and the variable @XmlData contains the actual xmldata. These get filled by the cursor which selects these from the Stg_Xml table.

Is there a simpler way of selecting all the applications of the files that are loaded in the Stg_Xml table? (Preferably in a set based way)
0
Comment
Question by:st0mpie
  • 2
3 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 22885779
here you go...just need to translate it to pull from a field rather than a var.

declare @x xml
set @x = '<data>
      <computer>
            <name>Computer B</name>
            <ip>192.168.0.2</ip>
            <applications>
                  <app name="Paint" version="0.8"/>
                  <app name="Visio" version="0.4"/>
                  <app name="Notepad" version="5.3"/>
            </applications>
      </computer>
</data>
'


select t.c.value('@name', 'varchar(50)'), t.c.value('@version', 'varchar(50)')
from @x.nodes('/data/computer/applications/app') t(c)
0
 

Author Comment

by:st0mpie
ID: 22885904
Thanks for your answer chapmandrew. I tried to describe that I know that is a way to do it. But this results in having to use a cursor, because there are more files in the Stg_Xml table.

Your solution is kindof the same as the statement I used in my question:

SELECT      @XmlFilename AS XmlFilename
,            applications.app.value('./@name','varchar(100)') AS [Name]
,            applications.app.value('./@version','varchar(100)') AS Version
FROM      @XmlData.nodes('//applications/app') AS applications(app)

The thing is that I would like one statement that gets the data of alle the files int the Stg_Xml table at once (set based). By using the statement you suggest I would still need two statements: one for xmlfile 1 and one for xmlfile 2. And if I load more xmlfiles into the table I would need even more. The only way to overcome this that I know of is by using a cursor.
0
 

Accepted Solution

by:
st0mpie earned 0 total points
ID: 22920351
The solution is using the cross apply:

SELECT       x.y.value('@name', 'VARCHAR(MAX)') AS [Name],
             x.y.value('@version', 'VARCHAR(MAX)') AS [Version]
FROM         Stg_Xml
CROSS APPLY  XMLData.nodes('data/computer/applications/app') as x(y)

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline

831 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