Hello, im new at xquery, so im little lost, my xml file have a different structure... so i would love if u can give me a little help...
< imovel > < referencia >48428< /referencia > < tipo id="1" / > < tipologia id="3" / > < estado>NOVO< /estado > < distrito id="14" / > < concelho id="1416" / > < freguesia id="141621" / > < /imovel > As u can see my xml dont have the "value" as ur example have...
so how it would be my select ?
this is your example
SELECT x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') AS CONFIG_ID, x.value('(Field[@Name="INST_ID"]/@Value)[1]','INT') AS INST_ID, x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') AS CHARC, x.value('(Field[@Name="VALUE"]/@Value)[1]','INT') AS VALUE FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)
In the next example i want three columns, the first "Referencia" , the select returns the value i want, in the second "tipologia" the select return "null" (dont know why ), and in the third "para_venda", the select return zero to this column, because dont have any value, what i would like to this column is, when appear on xml returns 1 if not returns 0 or null
SELECT x.value('referencia[1]','INT') AS referencia, -- WORKS PERFECT x.value('tipologia[1]','INT') AS tipologia, --SHOW ME NULL ON SELECT x.value('para_venda[1]','BIT') AS para_venda -- I want to return 1 FROM @x.nodes('/imovel') d(x)
SELECT x.value('referencia[1]','INT') AS referencia, x.value('(tipologia/@id)[1]','INT') AS tipologia, CASE WHEN x.exist('para_venda') = 1 THEN 1 ELSE 0 END AS para_venda FROM @x.nodes('/imovel') d(x)
/* OUTPUT:
referencia tipologia para_venda ----------- ----------- ----------- 48428 3 1 */
Posts
Hello, im new at xquery, so im little lost, my xml file have a different structure... so i would love if u can give me a little help...
ReplyDelete< imovel >
< referencia >48428< /referencia >
< tipo id="1" / >
< tipologia id="3" / >
< estado>NOVO< /estado >
< distrito id="14" / >
< concelho id="1416" / >
< freguesia id="141621" / >
< /imovel >
As u can see my xml dont have the "value" as ur example have...
so how it would be my select ?
this is your example
SELECT
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') AS CONFIG_ID,
x.value('(Field[@Name="INST_ID"]/@Value)[1]','INT') AS INST_ID,
x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') AS CHARC,
x.value('(Field[@Name="VALUE"]/@Value)[1]','INT') AS VALUE
FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)
Best Regards
Sure, I can help you.
ReplyDeleteWhat is the output that you are expecting from this SAMPLE xml document?
hello again, and thanks for answer so quick...
ReplyDeleteLets see if my english can help my explanation :)
In the next example i want three columns, the first "Referencia" , the select returns the value i want, in the second "tipologia" the select return "null" (dont know why ), and in the third "para_venda", the select return zero to this column, because dont have any value, what i would like to this column is, when appear on xml returns 1 if not returns 0 or null
DECLARE @x XML
SELECT @x = '< imovel >
< referencia >48428< /referencia >
< tipologia id="3" />
< para_venda / >
< /imovel >'
SELECT
x.value('referencia[1]','INT') AS referencia, -- WORKS PERFECT
x.value('tipologia[1]','INT') AS tipologia, --SHOW ME NULL ON SELECT
x.value('para_venda[1]','BIT') AS para_venda -- I want to return 1
FROM @x.nodes('/imovel') d(x)
Thanks for ur help me again...
:)
Try this:
ReplyDeleteDECLARE @x XML
SELECT @x = '
< imovel >
< referencia >48428< /referencia >
< tipologia id="3"/ >
< para_venda / >
< /imovel >'
SELECT
x.value('referencia[1]','INT') AS referencia,
x.value('(tipologia/@id)[1]','INT') AS tipologia,
CASE
WHEN x.exist('para_venda') = 1 THEN 1
ELSE 0
END AS para_venda
FROM @x.nodes('/imovel') d(x)
/*
OUTPUT:
referencia tipologia para_venda
----------- ----------- -----------
48428 3 1
*/
Thanks Thanks Thanks, it workssss,
ReplyDeleteYou're the Man:)