CODEWITHSUNDEEP
sql-serverxml
murtaza
murtaza

Reputation: 21

Select XML nodes in SQL Server

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?

Upvotes: 2

Views: 3174

Answers (1)

marc_s
marc_s

Reputation: 754258

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.....

Upvotes: 3

Related Questions