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)
July 22, 2020 at 12:31 pm
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
July 23, 2020 at 9:35 am
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
July 23, 2020 at 9:48 am
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)
July 23, 2020 at 9:51 am
This was removed by the editor as SPAM
July 23, 2020 at 10:10 am
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.
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?
July 23, 2020 at 10:22 am
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.
July 25, 2020 at 7:21 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply