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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
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
st0mpieAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.