Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/