Is it possible to load such XML file using SQLXML BulkLoad?

Hello
I have an XML file:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
</Item>
<Item>
<ItemID>2</ItemID>
<Property1>...</Property1>
<Property2>...</Property2>
<Property3>...</Property3>
<Property4>...</Property3>
...
</Item>
...
</root>
Number of properties for <Item> is not fixed and their names also not
defined (except ItemID). Is it possible to create XSD schema for
importing this into table using SQLXML BulkLoad facility?
Table:
ItemID PropertyName PropertyValue
=================================
1 Property1 ...
1 Property2 ...
1 Property3 ...
2 Property1 ...
2 Property2 ...
2 Property3 ...
2 Property4 ...
...
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
You cannot map names of elements into data using the schema mapping. You
need to use OpenXML for such mappings.
HTH
Michael
"jimmers" <jimmers@.yandex.ru> wrote in message
news:b0ede647.0501200116.5025661d@.posting.google.c om...
> Hello
> I have an XML file:
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> </Item>
> <Item>
> <ItemID>2</ItemID>
> <Property1>...</Property1>
> <Property2>...</Property2>
> <Property3>...</Property3>
> <Property4>...</Property3>
> ...
> </Item>
> ...
> </root>
> Number of properties for <Item> is not fixed and their names also not
> defined (except ItemID). Is it possible to create XSD schema for
> importing this into table using SQLXML BulkLoad facility?
> Table:
> ItemID PropertyName PropertyValue
> =================================
> 1 Property1 ...
> 1 Property2 ...
> 1 Property3 ...
> 2 Property1 ...
> 2 Property2 ...
> 2 Property3 ...
> 2 Property4 ...
> ...
>
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
|||No, not this exact data file can be mapped using XSD schema. Use XSLT to
transform this data file to look something like, .
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Item>
<ItemID>1</ItemID>
<Property><Name>Property1</Name><Value>...</Value></Property>
<Property><Name>Property2</Name><Value>...</Value></Property>
<Property><Name>Property3</Name><Value>...</Value></Property>
</Item>
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
> You cannot map names of elements into data using the schema mapping. You
> need to use OpenXML for such mappings.
> HTH
> Michael
> "jimmers" <jimmers@.yandex.ru> wrote in message
> news:b0ede647.0501200116.5025661d@.posting.google.c om...
>
|||Hello Michael and Chandra
First of all, thank you for prompt responses.
Unfortunately I cannot do XSLT transformation because input file size is
huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML input
file and simple XSLT file. The program executed approximately 10 minutes and
then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
Windows 2003 Server Standard Edition.
Right now I stick with the following solution: console application reads
elements from input file with help of XmlTextReader class until size
threshold is reached and saves them in temporary files. Then each resulting
file contents is passed to stored procedure that utilized OPENXML. In other
words, I had to duplicate SQLXMLBulkLoad functionality in Transactional mode
and then use OPENXML feature.
By the way, what is the meaning of HTH signature?
Thank you
Martin Rakhmanov
jimmers@.yandex.ru
"Chandra Kalyanaraman [MSFT]" wrote:

> No, not this exact data file can be mapped using XSD schema. Use XSLT to
> transform this data file to look something like, .
> <?xml version="1.0" encoding="UTF-8"?>
> <root>
> <Item>
> <ItemID>1</ItemID>
> <Property><Name>Property1</Name><Value>...</Value></Property>
> <Property><Name>Property2</Name><Value>...</Value></Property>
> <Property><Name>Property3</Name><Value>...</Value></Property>
> </Item>
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekbVtMv$EHA.1188@.tk2msftngp13.phx.gbl...
>
>
|||Hi Martin.
300MB XML file: What on earth do you have in that? :-) XML files should be
kept as small as possible and not be used as a "database replacement". :-)
HTH means: Hope This Helps
Best regards
Michael
"jimmers" <jimmers@.discussions.microsoft.com> wrote in message
news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...[vbcol=seagreen]
> Hello Michael and Chandra
> First of all, thank you for prompt responses.
> Unfortunately I cannot do XSLT transformation because input file size is
> huge (~300 Mb): I made test with .NET XslTransform class on 100 Mb XML
> input
> file and simple XSLT file. The program executed approximately 10 minutes
> and
> then out-of-memory exception was thrown. On disk I got incomplete 42 Mb
> result file. The server has 512 Mb memory, P4 2.8 GHz CPU and runs under
> Windows 2003 Server Standard Edition.
> Right now I stick with the following solution: console application reads
> elements from input file with help of XmlTextReader class until size
> threshold is reached and saves them in temporary files. Then each
> resulting
> file contents is passed to stored procedure that utilized OPENXML. In
> other
> words, I had to duplicate SQLXMLBulkLoad functionality in Transactional
> mode
> and then use OPENXML feature.
> By the way, what is the meaning of HTH signature?
> Thank you
> Martin Rakhmanov
> jimmers@.yandex.ru
>
> "Chandra Kalyanaraman [MSFT]" wrote:
|||300 Mb XML file is maximum size, normally it will be about 100 Mb. It is
generated by external system and I cannot affect this unfortunately.
Thank you
Martin
"Michael Rys [MSFT]" wrote:

> Hi Martin.
> 300MB XML file: What on earth do you have in that? :-) XML files should be
> kept as small as possible and not be used as a "database replacement". :-)
> HTH means: Hope This Helps
> Best regards
> Michael
> "jimmers" <jimmers@.discussions.microsoft.com> wrote in message
> news:214EF7CD-F4BB-4E5A-AD6C-1A57CFA00C12@.microsoft.com...
>
>