asked on

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)

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

st0mpie

ASKER

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.

ASKER CERTIFIED SOLUTION

st0mpie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial