How to read xml column?

Question

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:

Which RDBMS do you use?

Mithrandir
– Mithrandir

2015-10-06 10:10:22 +00:00
Commented Oct 6, 2015 at 10:10 — Mithrandir
– Mithrandir, Commented Oct 6, 2015 at 10:10

calinaadi · Accepted Answer · 2015-10-06 14:00:53Z

1

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

edited Oct 6, 2015 at 14:00

answered Oct 6, 2015 at 10:47

calinaadi

1,4661 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samanth Over a year ago

i can not system will generate in above format only .

Collectives™ on Stack Overflow

How to read xml column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related