sql server programming: How do I insert rows to a table of Database from a XML file stored in somewhere in hard disk -- sql server programming

How do I insert rows to a table of Database from a XML file stored in somewhere in hard disk

Orgil
4/3/2007 10:31:29 PM

sql server programming: I have a XML file. Its physical file path is "C:\Dursgal.xml".
There is a posibility to get the xml file's rows to a table in SQL
database. I use MSSQL 2000.
The xml file's some nodes are:
<Dursgal>
<UNBMS_ID>1122=D0=91=D0=9E=D0=9C00003</UNBMS_ID>
<IsDursgal>true</IsDursgal>
<ID_Zereglel>168</ID_Zereglel>
<ID_Umchlugch>71</ID_Umchlugch>
<ID_Ezemshigch>1</ID_Ezemshigch>
<Name>=D3=A8=D0=BD=D0=B4=D3=A9=D1=80 =D0=B3=D1=8D=D0=B3=D1=8D=D1=8D=D0=BD=
=D0=B8=D0=B9 =D0=BE=D0=BB=D0=B1=D0=BE=D0=B3</Name>
<PrivateNum>7845</PrivateNum>
<ID_SanHumrug>1</ID_SanHumrug>
<ID_SubType>7</ID_SubType>
<ID_Buteelch>29</ID_Buteelch>
</Dursgal>
- <Dursgal>
<UNBMS_ID>1122=D0=91=D0=9E=D0=9C00004</UNBMS_ID>
<IsDursgal>true</IsDursgal>
<ID_Umchlugch>86</ID_Umchlugch>
<ID_Ezemshigch>1</ID_Ezemshigch>
<Address>asdsadf</Address>
<Name>=D0=9D=D0=B0=D0=B9=D0=B4=D0=B0=D0=BD</Name>
<NameOther>=D1=8B=D0=B1=D3=A9=D1=8B=D0=B1=D3=A9=D1=8B=D0=B1=D3=A9</NameOt=
her>
<PrivateNum>73-723</PrivateNum>
<ID_SanHumrug>3</ID_SanHumrug>
</Dursgal>

I've wasted many times to do my task, but I can't get any positive
results.
If it is posible, tell me please.

Re: How do I insert rows to a table of Database from a XML file stored in somewhere in hard disk

xyb
4/3/2007 11:44:32 PM

On 4=E6=9C=884=E6=97=A5, =E4=B8=8B=E5=8D=881=E6=97=B631=E5=88=86, "Orgil" <=
[quoted text, click to view]

it is posible.some code here and you can see more infomation about
OPENXML at BOL to specify your table schema and xml schema.

create table #txml
(
cxml varchar(8000)
)

insert into #txml exec master..xp_cmdshell 'type c:\\Dursgal.xml'

declare @varchar(8000)

select @varchar =3D cxml from #txml
declare @hDoc int
exec sp_xml_preparedocument @hDoc output,@varchar

insert into tablename
select * from
openxml(@hDoc,'//Dursgal')
with tablename

I am working at sqlserver2005 now so this code has not been well
tested at sql2000,i hope it can show you a hint.