SQL, XML, Xpath, SQL Server 2005

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)
st0mpieAsked:
Who is Participating?
 
st0mpieConnect With a Mentor Author Commented:
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
 
chapmandewConnect With a Mentor Commented:
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
 
st0mpieAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.