insert xml into sql server
- by JonDog
ok, so I know there are a bunch of other post on importing xml to sql server but I just cant seem to figure it out. I think my problem may have to do with having multi-levels. Can anyone help please. My xml, sql table, and the code i've tried.
thanks in advance.
<items>
<item>
<sku>
<value>
the_sku
</value>
</sku>
<short_dis2>
<value>
short_discription2
<value/>
</short_dis2>
<short_dis1>
<value>
short_discription1
</value>
</short_dis1>
<title>
<value>
product_title
</value>
</title>
<detailSpec>
<value>
detailed_specification_html
</value>
</detailSpec>
<basicSpec>
<value>
basic_overview_html
</value>
</basicSpec>
<basicSpecHeading>
<value>
besic_spec_heading
</value>
</basicSpecHeading>
<detailSpecHeading>
<value>
detailed_specifications_heading
</value>
</detailSpecHeading>
<model>
<value>
the_model_number
</value>
</model>
<image_file_name>
<value>
the_image_url
</value>
</image_file_name>
</item>
...
CREATE TABLE [dbo].[products](
[sku] [nchar](15) NOT NULL,
[model] [nvarchar](50) NULL,
[title] [ntext] NULL,
[short_dis1] [ntext] NULL,
[short_dis2] [ntext] NULL,
[basicSpecHeading] [ntext] NULL,
[basicSpec] [ntext] NULL,
[detailSpecHeading] [ntext] NULL,
[detailSpec] [ntext] NULL,
[image_file_name] [nchar](100) NULL
INSERT INTO products (sku, short_dis2,short_dis1,title,detailSpec,basicSpec,basicSpecHeading,detailSpecHeading,model,image_file_name)
SELECT X.product.query('sku').value('.', 'nchar(15)'),
X.product.query('short_dis2').value('.', 'nvarchar(max)'),
X.product.query('short_dis1').value('.', 'nvarchar(max)'),
X.product.query('title').value('.', 'nvarchar(max)'),
X.product.query('detailSpec').value('.', 'nvarchar(max)'),
X.product.query('basicSpec').value('.', 'nvarchar(max)'),
X.product.query('basicSpecHeading').value('.', 'nvarchar(max)'),
X.product.query('detailSpecHeading').value('.', 'nvarchar(max)'),
X.product.query('model').value('.', 'nvarchar(max)'),
X.product.query('image_file_name').value('.', 'nvarchar(max)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'C:\users\me\desktop\xml_sample.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);
