SQL Server stored procedure to read xml file

Question

I have an application where I want to list all the products that matches requested filter category. I can send all the filters from c# code via XML file. my XML file would look like...

<filter CategoryId="" ForHomepage="" StartingIndex="">
<brand>
<id></id>
<id></id>
</brand>
<os>
<id></id>
<id></id>
</os>
<touch value="" />
<display>
<id></id>
<id></id>
</display>
<ram>
<id></id>
<id></id>
</ram>
<storage>
<id></id>
<id></id>
</storage>
<camera>
<id></id>
<id></id>
</camera>
<battery>
<id></id>
<id></id>
</battery>
</filter>

I can read the XML and store the data in temp table but I want to read categoryid, touch, forhomepage and startingindex fields in variables declared in sp (an not in table because this is non repetitive data).

Can anyone have such issues in past? To store the XML data in declared variables.

Taken from OP's comment:

<filter CategoryId="12" ForHomepage="true" StartingIndex="0">
  <brand>
    <id>1001</id>
    <id>1006</id>
  </brand>
  <os>
    <id>7005</id>
    <id>7009</id>
  </os>
  <touch value="true" />
  <display>
    <id>3002</id>
    <id>3005</id>
  </display>
  <ram>
    <id>2006</id>
    <id>2009</id>
  </ram>
  <storage>
    <id>4006</id>
  </storage>
  <camera>
    <id>9009</id>
    <id>9014</id>
  </camera>
  <battery>
    <id>1501</id>
    <id>1581</id>
  </battery>
</filter>

Please add your XML filled with data. I suppose, that there's no need to read your values into declared variables (as you demand), but better use a CTE to get your values for a inline / set-based ad-hoc query. Please give more details! — Gottfried Lesigang
– Gottfried Lesigang, Commented Jul 11, 2016 at 13:05
<filter CategoryId="12" ForHomepage="true" StartingIndex="0"> <brand> <id>1001</id> <id>1006</id> </brand> <os> <id>7005</id> <id>7009</id> </os> <touch value="true" /> <display> <id>3002</id> <id>3005</id> </display> <ram> <id>2006</id> <id>2009</id> </ram> <storage> <id>4006</id> </storage> <camera> <id>9009</id> <id>9014</id> </camera> <battery> <id>1501</id> <id>1581</id> </battery> </filter> — AChauhan
– AChauhan, Commented Jul 11, 2016 at 13:10
thanks for your prompt reply Shnugo... I am looking for CTE and will update the solution, if i get. Thanks — AChauhan
– AChauhan, Commented Jul 11, 2016 at 13:11

Gottfried Lesigang · Accepted Answer · 2016-07-12 08:14:55Z

This is - for sure! - not exactly what you are looking for (I just don't know enough about what you are going to do with this), but it should point you to the right direction:

declare @xml XML=
'<filter CategoryId="12" ForHomepage="true" StartingIndex="0">
  <brand>
    <id>1001</id>
    <id>1006</id>
  </brand>
  <os>
    <id>7005</id>
    <id>7009</id>
  </os>
  <touch value="true" />
  <display>
    <id>3002</id>
    <id>3005</id>
  </display>
  <ram>
    <id>2006</id>
    <id>2009</id>
  </ram>
  <storage>
    <id>4006</id>
  </storage>
  <camera>
    <id>9009</id>
    <id>9014</id>
  </camera>
  <battery>
    <id>1501</id>
    <id>1581</id>
  </battery>
</filter>';

WITH MyValues AS
(
    SELECT flt.value('@CategoryId','int') AS CategoryId
          ,flt.value('@ForHomepage','bit') AS ForHomepage
          ,flt.value('@StartingIndex','int') AS StartingIndex
          ,elmt.value('local-name(.)','varchar(max)') AS Filter
          ,elmt.value('id[1]','int') AS ID1
          ,elmt.value('id[2]','int') AS ID2
    FROM @xml.nodes('/filter') AS A(flt)
    CROSS APPLY A.flt.nodes('*') AS B(elmt)
)
SELECT * FROM MyValues

The result

+------------+-------------+---------------+---------+------+------+
| CategoryId | ForHomepage | StartingIndex | Filter  | ID1  | ID2  |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | brand   | 1001 | 1006 |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | os      | 7005 | 7009 |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | touch   | NULL | NULL |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | display | 3002 | 3005 |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | ram     | 2006 | 2009 |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | storage | 4006 | NULL |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | camera  | 9009 | 9014 |
+------------+-------------+---------------+---------+------+------+
| 12         | 1           | 0             | battery | 1501 | 1581 |
+------------+-------------+---------------+---------+------+------+

UPDATE

According to your comment you need the first three in variables...

Use the same XML-variable as above and try this:

DECLARE @CategoryId INT;
DECLARE @ForHomepage BIT;
DECLARE @StartingIndex INT;

SELECT @[email protected]('(/filter/@CategoryId)[1]','int')
      ,@[email protected]('(/filter/@ForHomepage)[1]','bit')
      ,@[email protected]('(/filter/@StartingIndex)[1]','int');

SELECT @CategoryId,@ForHomepage,@StartingIndex

This will also work for me, but i want to store CategoryId | ForHomepage | StartingIndex in declared variables and not in Table columns. I am so sorry if i was not clear in my question.
Thanks it is working now. Thanks a lot for your kind help and time.

Collectives™ on Stack Overflow

SQL Server stored procedure to read xml file

1 Answer 1

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related