August 23, 2010 at 8:15 am
Dear all,
I have a XML database Ex,
Validated DTD is
<?xml version="1.0"?>
<!ELEMENT note (to,from,address,content)>
<!ELEMENT to (#PCDATA)>
<!ELEMENT from (#PCDATA)>
<!ELEMENT address(#PCDATA)>
<!ELEMENT content(#PCDATA)>
the xml is
<?xml version="1.0" ?>
<!DOCTYPE table !DOCTYPE table SYSTEM "table.dtd">
- <table>
<to>sara</to>
<from>USA</from>
<address>Test</address>
<content>Hi how are you</content>
</table>
I want to import into one table with table as table name the columns are to,from,address,content
Please help me to load this file, am searching many BOL but i have not get clearly, all are showing C#or vb coding.. in sql i need
Thanks in advance
August 23, 2010 at 1:18 pm
Hi Saravanan,
I am not really sure what you are trying to do, but the first thing you have to do is convert the xml using convert with the style of 2 before SQL will even look at the XML. After that you can use the nodes/value method to extract the data.
Here are a few examples of how to extract the data.
DECLARE @x XML
SET @x=
CONVERT(XML,'<?xml version="1.0" ?>
<!DOCTYPE table SYSTEM "table.dtd">
<table>
<to>sara</to>
<from>USA</from>
<address>Test</address>
<content>Hi how are you</content>
</table>',2)
--Data as columns
SELECT
@x.value('(/table/to)[1]','varchar(50)'),
@x.value('(/table/from)[1]','varchar(50)'),
@x.value('(/table/address)[1]','varchar(50)'),
@x.value('(/table/content)[1]','varchar(50)')
--Data as rows
SELECT
y.i.value('local-name(.)','varchar(50)') AS colname,
y.i.value('.','varchar(50)') AS colvalue
FROM @x.nodes('//*') y(i)
WHERE y.i.value('local-name(.)','varchar(50)') <> 'table'
--Data as columns
SELECT [to],[from],[address],[content]
FROM(
SELECT
y.i.value('local-name(.)','varchar(50)') AS colname,
y.i.value('.','varchar(50)') AS colvalue
FROM @x.nodes('//*') y(i)
WHERE y.i.value('local-name(.)','varchar(50)') <> 'table'
) AS x
PIVOT(MAX(colvalue) FOR colname IN([to],[from],[address],[content])) AS pvt
August 23, 2010 at 11:22 pm
Actually what am trying to do is ,
I have number of xml file with same template, i want to load my master metadata table , but i dont have a knowledge in front programming, only sql server i have to do, can u help me?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply