Use XQuery to get at this data

Question

I am new to XQuery and am having some problems with it. Here is my example.

I have this variable:

declare @xmlDoc XML

it has the following xml stored in it:

<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="Sharedparam" type="xs:string" minOccurs="0" />
                <xs:element name="Antoher" type="xs:string" minOccurs="0" />
                <xs:element name="RandomParam2" type="xs:string" minOccurs="0" />
                <xs:element name="MoreParam" type="xs:string" minOccurs="0" />
                <xs:element name="ResultsParam" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table1>
    <Sharedparam>shared</Sharedparam>
    <Antoher>sahre</Antoher>
    <RandomParam2>Good stuff</RandomParam2>
    <MoreParam>and more</MoreParam>
    <ResultsParam>2</ResultsParam>
  </Table1>
  <Table1>
    <Sharedparam>Hey</Sharedparam>
    <Antoher>what </Antoher>
    <RandomParam2>do you</RandomParam2>
    <MoreParam>think</MoreParam>
    <ResultsParam>2</ResultsParam>
  </Table1>
  <Table1 />
</NewDataSet>

How can I select all the values of Sharedparam? (Or really any decent query that returns values (not xml) would be great.)

What I am really looking to do is get a result set like this:

Name             Value1          Value2          Value3        Value4
Sharedparam      shared          Hey             Null          Null
Another          share           what            Null          Null
....

This would have me ignoring any data beyond "Value4" (and that is acceptable for my use of this data).

That's an odd layout. You expect columns Sharedparam,Antoher etc: not rows. And if i read that right, Table1 `maxOccurs="unbounded"` which means an variable number of columns = not SQL — gbn, May 24 '11 at 19:09

marc_s · Accepted Answer · 2011-05-24T20:26:00.853

Try something like this:

SELECT
    TBL.SParam.value('(.)[1]', 'varchar(50)')
FROM
    @xmldoc.nodes('/NewDataSet/Table1/Sharedparam') AS TBL(SParam)

Gives me an output of:

(No column name)
shared
Hey

Update: if you want to get at all the XML elements and their values inside the <Table1> elements, you can use this XQuery:

SELECT
    TBL.SParam.value('local-name(.)[1]', 'varchar(50)') 'Attribute',
    TBL.SParam.value('(.)[1]', 'varchar(50)') 'Value'
FROM
    @xmldoc.nodes('/NewDataSet/Table1/*') AS TBL(SParam)

which outputs:

Attribute            Value
Sharedparam          shared
Antoher              sahre
RandomParam2         Good stuff
MoreParam            and more
ResultsParam         2
Sharedparam          Hey
Antoher              what 
RandomParam2         do you
MoreParam            think
ResultsParam         2

Update #2: to get the values of the first <Table1> and the second <Table1> XML node next to one another, you need to do two calls to .nodes() - once retrieving the first node, the other time the second one. It gets a bit hairy, especially if you want to extend that even further - and performance is going to be abysmal - but it works :-)

SELECT
    TBL.SParam.value('local-name(.)[1]', 'varchar(50)') 'Attribute',
    TBL.SParam.value('(.)[1]', 'varchar(50)') 'Value 1',
    TBL2.SParam2.value('(.)[1]', 'varchar(50)') 'Value 2'
FROM
    @xmldoc.nodes('/NewDataSet/Table1[1]/*') AS TBL(SParam)
INNER JOIN
    @xmldoc.nodes('/NewDataSet/Table1[2]/*') AS TBL2(SParam2) ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)') = TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')

Gives an output of:

Attribute      Value 1     Value 2
Sharedparam    shared       Hey
ResultsParam      2          2
RandomParam2   Good stuff   do you
Antoher        sahre        what 
MoreParam      and more     think

Thanks for the great help! I thought I could take it the rest of the way by my self, but I am stuck... Is there a way to get the values for both Table[1] and Table[2] on the same row next to the name? (like the example output in my question) I can do it for a single "attribute". But I can't seem to get it to repeat for the rest of the attributes. — Vaccano, May 24 '11 at 20:17

score 2 · Answer 2 · answered May 24 '11 at 19:10

That's an odd layout. You expect columns Sharedparam,Antoher etc: not rows.

And if I read that right, Table1 maxOccurs="unbounded" which means an variable number of columns = not SQL which is fixed column type and number

To read each tag as a column (which are fixed and finite) you'd do this:

SELECT
   x.item.value('(Sharedparam)[1]', 'varchar(100)') AS Sharedparam,
   x.item.value('(Antoher)[1]', 'varchar(100)') AS Antoher,
   x.item.value('(SharedRandomParam2param)[1]', 'varchar(100)') AS RandomParam2,
   ...
FROM
   @xmlDoc.nodes('/NewDataSet/Table1') x(item)

Use XQuery to get at this data

2 Answers2

Linked