using xml

[Seaspray0]

I want to import a webpage into the database "using xml" but the help only covers a basic tree structure. The structure I wish to import has multiple branches similar to the following:

<root>
<item timestamp="yyyy-mm-ddThh:mm:ss">
<Identity Author="name">
<pubname>publisher</pubname>
<language>english<language>
</identity>
<description timestamp="yyyy-mm-ddThh:mm:ss">
<title>text</title>
</description>
</item>
... more items
</root>

All the items contain the same tree format. I'd be happy with a select statement that shows all the information associated with each item. I could go from there.

A+/MCP/MCSE/MCDBA

 

[hmckillop]

try

declare @ssql varchar(1000), @idoc int
set @ssql = 
'<root>
  <item timestamp="yyyy-mm-ddThh:mm:ss">
    <identity Author="name">
      <pubname>publisher</pubname>
      <language>english</language>
    </identity>
    <description timestamp="yyyy-mm-ddThh:mm:ss">
      <title>text</title>
    </description>
  </item>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @ssql
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/root/item',1)
            WITH (
					timestamp  varchar(20) '@timestamp',
				  	author varchar(10) 'identity/@Author',
					pubname varchar(10) 'identity/pubname',
					language varchar(10) 'identity/language',
					descriptiontime varchar(10) 'description/@timestamp',
					language varchar(10) 'description/title'
                --  ContactName varchar(20)
				)
EXEC sp_xml_removedocument @idoc

might need to adjust types

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Seaspray0]

Beautiful! Thank you.

A+/MCP/MCSE/MCDBA