Get multiple values out of xml

  • Hi I have the following Syntax:

     

    DECLARE @MyXMLString XML
    SET @MyXMLString =
    '<items>
    <item>
    <bezeichnung>Woonkamer + Eetkamer</bezeichnung>
    <typ>Woonkamer</typ>
    <typvalue>1</typvalue>
    <stock>1</stock>
    <lange>11.3</lange>
    <breite>3.86</breite>
    <lineardisabled />
    <m2>43.62</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>109.05</m3>
    <angaben>Zuluft</angaben>
    <zuld>151</zuld>
    <zuld_s />
    <abld>0</abld>
    <abld_s />
    <rid>1</rid>
    <id>1</id>
    </item>
    <item>
    <bezeichnung>Keuken</bezeichnung>
    <typ>Open keuken</typ>
    <typvalue>6</typvalue>
    <stock>1</stock>
    <lange>3.82</lange>
    <breite>2.42</breite>
    <lineardisabled />
    <m2>9.24</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>23.1</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>75</abld>
    <abld_s />
    <rid>2</rid>
    <id>2</id>
    </item>
    <item>
    <bezeichnung>Berging</bezeichnung>
    <typ>Andere extractie</typ>
    <typvalue>11</typvalue>
    <stock>1</stock>
    <lange>2.3</lange>
    <breite>2</breite>
    <lineardisabled />
    <m2>4.6</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>11.5</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>40</abld>
    <abld_s>3.4782608695652173</abld_s>
    <rid>3</rid>
    <id>3</id>
    </item>
    <item>
    <bezeichnung>WC1</bezeichnung>
    <typ>WC</typ>
    <typvalue>9</typvalue>
    <stock>1</stock>
    <lange>1.68</lange>
    <breite>1</breite>
    <lineardisabled />
    <m2>1.68</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>4.2</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>25</abld>
    <abld_s />
    <rid>4</rid>
    <id>4</id>
    </item>
    <item>
    <bezeichnung>Slaapkamer 1 + Dressing</bezeichnung>
    <typ>Slaapkamer</typ>
    <typvalue>2</typvalue>
    <stock>2</stock>
    <lange />
    <breite />
    <lineardisabled />
    <m2>21.53</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>53.825</m3>
    <angaben>Zuluft</angaben>
    <zuld>73</zuld>
    <zuld_s />
    <abld>0</abld>
    <abld_s />
    <rid>5</rid>
    <id>5</id>
    </item>
    <item>
    <bezeichnung>Douche</bezeichnung>
    <typ>Badkamer</typ>
    <typvalue>7</typvalue>
    <stock>2</stock>
    <lange>2.3</lange>
    <breite>2</breite>
    <lineardisabled />
    <m2>4.6</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>11.5</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>50</abld>
    <abld_s />
    <rid>6</rid>
    <id>6</id>
    </item>
    <item>
    <bezeichnung>Badkamer</bezeichnung>
    <typ>Badkamer</typ>
    <typvalue>7</typvalue>
    <stock>2</stock>
    <lange>2.86</lange>
    <breite>3.2</breite>
    <lineardisabled />
    <m2>9.15</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>22.875</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>50</abld>
    <abld_s />
    <rid>7</rid>
    <id>7</id>
    </item>
    <item>
    <bezeichnung>Wasplaats</bezeichnung>
    <typ>Was-, droogplaats</typ>
    <typvalue>8</typvalue>
    <stock>2</stock>
    <lange>2.86</lange>
    <breite>1.2</breite>
    <lineardisabled />
    <m2>3.43</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>8.575000000000001</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>50</abld>
    <abld_s />
    <rid>8</rid>
    <id>8</id>
    </item>
    <item>
    <bezeichnung>Slaapkamer 2</bezeichnung>
    <typ>Slaapkamer</typ>
    <typvalue>2</typvalue>
    <stock>2</stock>
    <lange />
    <breite />
    <lineardisabled />
    <m2>14.78</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>36.949999999999996</m3>
    <angaben>Zuluft</angaben>
    <zuld>54</zuld>
    <zuld_s />
    <abld>0</abld>
    <abld_s />
    <rid>9</rid>
    <id>9</id>
    </item>
    <item>
    <bezeichnung>WC</bezeichnung>
    <typ>WC</typ>
    <typvalue>9</typvalue>
    <stock>2</stock>
    <lange>1.68</lange>
    <breite>1</breite>
    <lineardisabled />
    <m2>1.68</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>4.2</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>25</abld>
    <abld_s />
    <rid>10</rid>
    <id>10</id>
    </item>
    <item>
    <bezeichnung>Slaapkamer 3</bezeichnung>
    <typ>Slaapkamer</typ>
    <typvalue>2</typvalue>
    <stock>3</stock>
    <lange>11.3</lange>
    <breite>2.86</breite>
    <lineardisabled />
    <m2>32.32</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>80.8</m3>
    <angaben>Zuluft</angaben>
    <zuld>72</zuld>
    <zuld_s />
    <abld>0</abld>
    <abld_s />
    <rid>11</rid>
    <id>11</id>
    </item>
    <item>
    <bezeichnung>Zolder</bezeichnung>
    <typ>Andere extractie</typ>
    <typvalue>11</typvalue>
    <stock>3</stock>
    <lange>3.3</lange>
    <breite>5.94</breite>
    <lineardisabled />
    <m2>19.602</m2>
    <km2 />
    <hohe>2.5</hohe>
    <m3>49.005</m3>
    <angaben>Abluft</angaben>
    <zuld>0</zuld>
    <zuld_s />
    <abld>35</abld>
    <abld_s>0.7142128354249566</abld_s>
    <rid>12</rid>
    <id>12</id>
    </item>
    </items>'

    SELECT
    XC.value('@id', 'int')
    FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)

    When i run this I get the result with 12 rows of NULL... That's not what I want. I need the ID's in the ROW.

    (No column name)

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

    NULL

  • This should get you what you are looking for

    SELECT 
    XT.XC.value('(id/text())[1]', 'int')
    FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)
  • Another way is to specify the full XML path in the where clause (the nodes operator) and then get the value of ID:

    SELECT 
    XC.value('.[1]', 'int')
    FROM
    @MyXMLString.nodes('/items/item/id') AS XT(XC)

    Adi

  • Thanks for the helping answers . Is there a possible way to get it in all colums in table format (So I dont want to get only an specific colum) :

    So everthing in ITEM but the columns inside ITEM.

    bezeichnung    typ      typvalue

    sdasdasd           2          213

    sdasdasd           2          213

    sdasdasd           2          213

    • This reply was modified 6 months ago by  GG_BI_GG.
    • This reply was modified 6 months ago by  GG_BI_GG.
  • Use something like this, and change your data types as needed

    SELECT 
    id = XT.XC.value('(id/text())[1]', 'int')
    , bezeichnung = XT.XC.value('(bezeichnung/text())[1]', 'varchar(20)')
    , typ = XT.XC.value('(typ/text())[1]', 'varchar(20)')
    , typvalue = XT.XC.value('(typvalue/text())[1]', 'int')
    , stock = XT.XC.value('(stock/text())[1]', 'int')
    , lange = XT.XC.value('(lange/text())[1]', 'decimal(8,2)')
    , breite = XT.XC.value('(breite/text())[1]', 'decimal(8,2)')
    , abld_s = XT.XC.value('(abld_s/text())[1]', 'decimal(18,16)')
    FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)
  • This was removed by the editor as SPAM

  • GG_BI_GG wrote:

    A, so you are forced to specify it. It is not possible to call all fields without specifying (*) (inside ITEM) and get every column back as you call a table.

    DesNorton wrote:

    Use something like this, and change your data types as needed

    SELECT 
    id = XT.XC.value('(id/text())[1]', 'int')
    , bezeichnung = XT.XC.value('(bezeichnung/text())[1]', 'varchar(20)')
    , typ = XT.XC.value('(typ/text())[1]', 'varchar(20)')
    , typvalue = XT.XC.value('(typvalue/text())[1]', 'int')
    , stock = XT.XC.value('(stock/text())[1]', 'int')
    , lange = XT.XC.value('(lange/text())[1]', 'decimal(8,2)')
    , breite = XT.XC.value('(breite/text())[1]', 'decimal(8,2)')
    , abld_s = XT.XC.value('(abld_s/text())[1]', 'decimal(18,16)')
    FROM
    @MyXMLString.nodes('/items/item') AS XT(XC)

    A, so you are forced to specify it. It is not possible to call all fields without specifying (*) (inside ITEM) and get every column back as you call a table.

     

    Other question is, why are you using TEXT() in the syntax.. Is this to transform the value always in text format?

     

  • I am not aware of any method similar to SELECT * FROM xml.

    I use the text() for performance.  If you look at the execution plans for the the same query (with and without the text()), you will see that the text() has a good performance improvement.

    The reason that it performs well is not 100% clear to me.  However, my understanding is that that when querying the xml, the engine shreds the xml to find the node, then extracts that, with the node (eg:  <id>12</id>), then shred that again later to get the data (eg: 12).  By adding the text(), it goes directly to the values within the tags.

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply