parse xml file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

parse xml file

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-19 : 07:30:38

I have an xml file that has contents in the format below --

<root>
<d myfield="2" aggg="xyz" m="1" />
<d myfield="3" aggg="jjjj" m="1" />
<d myfield="4" aggg="test" m="1" />
</root>

How can I insert these to a table (with fiels corresponding to the xml -- myfield,agggg,m

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 07:36:24

[code]DECLARE @Sample XML

SET @Sample = '<root>
<d myfield="2" aggg="xyz" m="1" />
<d myfield="3" aggg="jjjj" m="1" />
<d myfield="4" aggg="test" m="1" />
</root>'

SELECT f.value('@myfield', 'INT') AS MyField,
f.value('@aggg', 'VARCHAR(MAX)') AS aggg,
f.value('@m', 'INT') AS m
FROM @Sample.nodes('/root/d') AS n(f)[/code]

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-19 : 07:59:57

thanks so much!

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-19 : 09:29:19

is there an easy way to get this to read a file instead of an xml string?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources