CODEWITHSUNDEEP
sqlsql-serverxml
Samanth
Samanth

Reputation: 374

How to read xml column?

I have an xml script in a customer table. Below structure is the Structure I need it in table for

<pricer xmlns="http://www.Card.com/xxxx/xxxx">
  <service>
    <response>
      <businessObjectModel>
        <Id>0</Id>
        <Card xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Card">
          <ID>1</ID>
          <Inc>0.0</Inc>
          <value>0.0</value>
          <Tvalue1>0.0</Tvalue1>
        </Card>
      </businessObjectModel>

I need like below table:

enter image description here

Upvotes: 0

Views: 56

Answers (1)

calinaadi
calinaadi

Reputation: 1466

If you want to iterate Card's you can do it like this:

   DECLARE @XML XML
SET @XML = '
<pricer xmlns:xsi="http://www.Card.com/xxxx/xxxx">
  <service>
    <response>
      <businessObjectModel>
        <Id>0</Id>
        <Card xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Card">
          <ID>1</ID>
          <Inc>0.0</Inc>
          <value>0.0</value>
          <Tvalue1>0.0</Tvalue1>
        </Card>
      </businessObjectModel>
      <businessObjectModel>
        <Id>1</Id>
        <Card xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Card">
          <ID>1</ID>
          <Inc>2.0</Inc>
          <value>1.0</value>
          <Tvalue1>2.0</Tvalue1>
        </Card>
      </businessObjectModel>
      </response></service></pricer>'

SELECT   
Tbl.Col.value('ID[1]', 'int'),
Tbl.Col.value('Inc[1]', 'decimal'),
Tbl.Col.value('value[1]', 'decimal'),
Tbl.Col.value('Tvalue1[1]', 'decimal')
FROM   @XML.nodes('pricer/service/response/businessObjectModel/Card') Tbl(Col)
GO

Upvotes: 1

Related Questions