CODEWITHSUNDEEP
sqlsql-serverxmlxml-parsing
Puzzled
Puzzled

Reputation: 63

Parsing nested XML into SQL table

What would be the right way to parse the following XML block into SQL Server table according to desired layout (below)? Is it possible to do it with a single SELECT statement, without UNION or a loop? Any takers? Thanks in advance. Input XML:

<ObjectData>
  <Parameter1>some value</Parameter1>
  <Parameter2>other value</Parameter2>
  <Dates>
    <dateTime>2011-02-01T00:00:00</dateTime>
    <dateTime>2011-03-01T00:00:00</dateTime>
    <dateTime>2011-04-01T00:00:00</dateTime>
  </Dates>
  <Values>
    <double>0.019974</double>
    <double>0.005395</double>
    <double>0.004854</double>
  </Values>
  <Description>
    <string>this is row 1</string>
    <string>this is row 2</string>
    <string>this is row 3</string>
  </Values>
</ObjectData>

Desired table output:

Parameter1  Parameter2      Dates               Values      Description

Some value  Other value 2011-02-01 00:00:00.0   0.019974    this is row 1
Some value  Other value 2011-03-01 00:00:00.0   0.005395    this is row 2
Some value  Other value 2011-04-01 00:00:00.0   0.004854    this is row 3

I am after an SELECT SQL statement using OPENXML or xml.nodes() functionality. For example, the following SELECT statement results in production between Values and Dates (that is all permutations of Values and Dates), which is something I want to avoid. 

SELECT 
doc.col.value('Parameter1[1]', 'varchar(20)') Parameter1, 
doc.col.value('Parameter2[1]', 'varchar(20)') Parameter2, 
doc1.col.value('.', 'datetime') Dates ,
doc2.col.value('.', 'float') [Values] 
FROM 
@xml.nodes('/ObjectData') doc(col),
@xml.nodes('/ObjectData/Dates/dateTime') doc1(col),
@xml.nodes('/ObjectData/Values/double') doc2(col);

Upvotes: 6

Views: 15080

Answers (3)

shimofuri
shimofuri

Reputation: 691

Use the OPENXML function. It is a rowset provider (it returns the set of rows parsed from the XML) and thus can be utilized in SELECT or INSERT like:

INSERT INTO table SELECT * FROM OPENXML(source, rowpattern, flags)

Please see the first example in the documentation link for clarity.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can make use of a numbers table to pick the first, second, third etc row from the child elements. In this query I have limited the rows returned to the number if dates provided. If there are more values or descriptions than dates you have to modify the join to take that into account.

declare @XML xml = '
<ObjectData>
  <Parameter1>some value</Parameter1>
  <Parameter2>other value</Parameter2>
  <Dates>
    <dateTime>2011-02-01T00:00:00</dateTime>
    <dateTime>2011-03-01T00:00:00</dateTime>
    <dateTime>2011-04-01T00:00:00</dateTime>
  </Dates>
  <Values>
    <double>0.019974</double>
    <double>0.005395</double>
    <double>0.004854</double>
  </Values>
  <Description>
    <string>this is row 1</string>
    <string>this is row 2</string>
    <string>this is row 3</string>
  </Description>
</ObjectData>'

;with Numbers as
(
  select number
  from master..spt_values
  where type = 'P'
)
select T.N.value('Parameter1[1]', 'varchar(50)') as Parameter1,
       T.N.value('Parameter2[1]', 'varchar(50)') as Parameter2,
       T.N.value('(Dates/dateTime[position()=sql:column("N.Number")])[1]', 'datetime') as Dates,
       T.N.value('(Values/double[position()=sql:column("N.Number")])[1]', 'float') as [Values],
       T.N.value('(Description/string[position()=sql:column("N.Number")])[1]', 'varchar(max)') as [Description]
from @XML.nodes('/ObjectData') as T(N)
  cross join Numbers as N
where N.number between 1 and (T.N.value('count(Dates/dateTime)', 'int'))

Upvotes: 7

paulsm4
paulsm4

Reputation: 121609

Typically, if you wanted to parse XML, you'd do it a programming language like Perl, Python, Java or C# that a) has an XML DOM, and b) can communicate with a relational database.

Here's a short article that shows you some of the basics of reading and writing XML in C# ... and even has an example of how to create an XML document from a SQL query (in one line!):

http://www.c-sharpcorner.com/uploadfile/mahesh/readwritexmltutmellli2111282005041517am/readwritexmltutmellli21.aspx

Upvotes: -1

Related Questions