Select XML nodes in SQL Server

Question

Hi I need to populate table by selecting value from child node

XML looks like this

<Transmitters>
  <Id>1</Id><CoverageLevel>2</CoverageLevel>
  <Id>2</Id><CoverageLevel>4</CoverageLevel>
  <Id>3</Id><CoverageLevel>6</CoverageLevel>
</Transmitters>

and table has two fields

Transmitter(Id, CoverageLevel)

INSERT INTO         
Transmitter([idTransmitter], [coverageLevel])

   SELECT       
      ParamValues.T.value('Id[1]', 'nvarchar(50)'),  
      ParamValues.T.value('CoverageLevel[1]', 'nvarchar(50)')
   FROM
      @otherTransmitter.nodes('//Transmitters') AS ParamValues(T)

but its not working?

marc_s · Accepted Answer · 2011-05-10 06:25:20Z

Your XML is not very well formatted for this kind of task - you don't have any useable child nodes inside <Transmitters> that you can depend on.

If your XML looked like this:

<Transmitters>
  <Transmitter>
    <Id>1</Id><CoverageLevel>2</CoverageLevel>
  </Transmitter>
  <Transmitter>
    <Id>2</Id><CoverageLevel>4</CoverageLevel>
  </Transmitter>
  <Transmitter>
    <Id>3</Id><CoverageLevel>6</CoverageLevel>
  </Transmitter>
</Transmitters>

then you could use the XPath expression

@otherTransmitter.nodes('/Transmitters/Transmitter') AS ParamValues(T)

to get a hold of your values.

You don't have that - you only have individual <Id> and <CoverageLevel> inside your <Transmitters> - there's no "container" XML tag that holds together those elements that belong together.

There's really no XPath that will allow you to properly enumerate those nodes.....

Collectives™ on Stack Overflow

Select XML nodes in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related