Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thusi
Starting Member
25 Posts |
 Posted - 2008-10-04 : 04:04:01
|
| Hi AllI want to import some data from an xml document into UDFs/SPs so that I can query other tables depending on the xml data. There're many examples out there which can be used to import data from xml into sql server tables, but my requirement is different to this. My xml is more like a template for a report/query I want to run. So for example, in the xml I may have:...<EligibilityCriteria> <MinPatientAge> 30 </MinPatientAge> <FundingStatus> funded </FundingStatus></EligibilityCriteria>...Of course this is a pretty simple example but you get the idea. To make things complex, I also have a few choice/optional tags, so not sure if this is even possible to do in TSQL. Once I've got all the variables extracted, I then want to run the normal SQL queries against existing tables with the variable in the WHERE clause.I can easily parse the xml in C# and then run the SPs from there by passing the parameters, so just wondering if you can do this parsing directly in sql server?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-04 : 06:48:16
|
you can parse xml in sql also. For this waht you need to do is to create a sp with xml parameter. then use xml functions like nodes(),query(),value() to parse the xml by specifying the xpath and then extract values to temp table and use it.for above xml if <Root> is root node of xml it will be likeSELECT x.v.value('./MinPatientAge[1]','int') AS Age, x.v.value('./FundingStatus[1]','varchar(10)') AS FundingStatusFROM @xml.nodes('/Root/EligibilityCriteria') AS x(v)Alternatively you could use OPENXML also to parse if its sql 2000 or below.Better to use xml datatype from SQL 2005 onwards |
 |
|
|
|
|
|
Extracting data from xml into variables