Re: Importing xml through tsql

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Assuming you want the data in two different tables, you could write:

declare @h int

exec sp_xml_preparedocument @h output, N'<myfile>

<table1>

<col1>Value1</col1>

<col2>Value2</col2>

<col3>Value3</col3>

<col4>Value4</col4>

</table1>

<table2>

<col1>Value1</col1>

<col2>Value2</col2>

<col3>Value3</col3>

<col4>Value4</col4>

</table2>

</myfile>'

--insert into table1

select * from OpenXML(@h, '/myfile/table1',2)

with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))

--insert into table2

select * from OpenXML(@h, '/myfile/table2',2)

with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))

exec sp_xml_removedocument @h

HTH

Michael

"Mal .mullerjannie@xxxxxxxxxxx>" <<removethis> wrote in message
news:D66EB2F0-A4F4-4CAB-904C-B4550359905B@xxxxxxxxxxxxxxxx
> Hey
>
> I'm relatively new to xml.
> I want to upload xml data with sql's openxml. I can do it with "normal"
> xml
> but my files look a bit different and I can't find help on it.
>
> My file looks like this:
>
> <myfile>
> <table1>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table1>
> <table2>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table2>
> </myfile>
>
> Hope this makes sense.
>
> Thanks for the help.
>


.

Relevant Pages

  • Re: Help needed with importing XML
    ... You could add a gratuitous identity column to #temp and use ... Those examples showed how to decompose arbitrary XML in multiple ... table1 and table2. ... But there has to be something in the relational schema tying table1 ...
    (microsoft.public.sqlserver.xml)
  • Re: Help needed with importing XML
    ... Those examples showed how to decompose arbitrary XML in multiple unrealated ... But there has to be something in the relational schema tying table1 ... and table2 together. ... by step example that will import the following xml file into two tables ...
    (microsoft.public.sqlserver.xml)
  • Re: Help needed with importing XML
    ... "Bob Beauchemin" wrote: ... Those examples showed how to decompose arbitrary XML in multiple ... table1 and table2. ... But there has to be something in the relational schema tying table1 ...
    (microsoft.public.sqlserver.xml)
  • Re: Help needed with importing XML
    ... Those examples showed how to decompose arbitrary XML in multiple unrealated ... But there has to be something in the relational schema tying table1 ... and table2 together. ... by step example that will import the following xml file into two tables ...
    (microsoft.public.sqlserver.xml)
  • Filtering and adding rows based on some condition
    ... DECLARE @TABLE1 TABLE ... (USGID INT NOT NULL,PCID INT NOT NULL) ... DECLARE @TABLE2 TABLE ... USGID PCID PCDESC Status ...
    (microsoft.public.sqlserver.programming)