f



Get XML values from nodes and child nodes

Hi everybody. My XML file is:
<Locatore>
  <NumeroProgressivo>001</NumeroProgressivo>
  <CodiceFiscale>CSTNDA69P90H523R</CodiceFiscale>
  <PersoneFisiche>
	<Cognome>CAST</Cognome>
	<Nome>NADIR</Nome>
	<Sesso>F</Sesso>
	<DataNascita>10091979</DataNascita>
	<ComuneNascita>RONCA</ComuneNascita>
	<ProvinciaNascita>BL</ProvinciaNascita>
  </PersoneFisiche>
</Locatore>
<Locatore>
  <NumeroProgressivo>002</NumeroProgressivo>
  <CodiceFiscale>PRSGRI74L29F443L</CodiceFiscale>
  <PersoneFisiche>
	<Cognome>PERISSI</Cognome>
	<Nome>IGOR</Nome>
	<Sesso>M</Sesso>
	<DataNascita>29071970</DataNascita>
	<ComuneNascita>MONTE</ComuneNascita>
	<ProvinciaNascita>SA</ProvinciaNascita>
  </PersoneFisiche>
</Locatore>

I need to get in ONE RECORD, both node value and his child nodes values, like this
001 | CSTNDA69P90H523R | CAST    | NADIR | F | 10091979 | RONCA | BL
002 | PRSGRI74L29F443L | PERISSI | IGOR  | M | 29071970 | MONTE | SA
.....

With this: 
SELECT 
X.valore.query('NumeroProgressivo').value('.', 'VARCHAR(20)') as NumeroProgressivo,
X.valore.query('CodiceFiscale').value('.', 'VARCHAR(16)') as CodiceFiscale

FROM Tbulk 
CROSS APPLY Tbulk.nodes('Fornitura/Documento/Soggetti/PrimoModulo/Locatore') AS X(valore);

I only get the first node values (NumeroProgressivo, CodiceFiscale), but not the child nodes (cognome, nome sesso...)
0
giancarlo
2/10/2014 10:20:41 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
605 Views

Similar Articles

[PageSpeed] 42

 (giancarlo.francesconi@gmail.com) writes:
> I only get the first node values (NumeroProgressivo, CodiceFiscale), but
> not the child nodes (cognome, nome sesso...) 
> 


DECLARE @xml xml = 
'<Locatore>
  <NumeroProgressivo>001</NumeroProgressivo>
  <CodiceFiscale>CSTNDA69P90H523R</CodiceFiscale>
  <PersoneFisiche>
	<Cognome>CAST</Cognome>
	<Nome>NADIR</Nome>
	<Sesso>F</Sesso>
	<DataNascita>10091979</DataNascita>
	<ComuneNascita>RONCA</ComuneNascita>
	<ProvinciaNascita>BL</ProvinciaNascita>
  </PersoneFisiche>
</Locatore>
<Locatore>
  <NumeroProgressivo>002</NumeroProgressivo>
  <CodiceFiscale>PRSGRI74L29F443L</CodiceFiscale>
  <PersoneFisiche>
	<Cognome>PERISSI</Cognome>
	<Nome>IGOR</Nome>
	<Sesso>M</Sesso>
	<DataNascita>29071970</DataNascita>
	<ComuneNascita>MONTE</ComuneNascita>
	<ProvinciaNascita>SA</ProvinciaNascita>
  </PersoneFisiche>
</Locatore>'

SELECT 
X.valore.query('NumeroProgressivo').value('.', 'VARCHAR(20)') as 
NumeroProgressivo,
X.valore.query('CodiceFiscale').value('.', 'VARCHAR(16)') as CodiceFiscale ,
Y.valore.query('Cognome').value('.', 'VARCHAR(16)') as Cognome,
Y.valore.query('Nome').value('.', 'VARCHAR(16)') as Nome,
Y.valore.query('Sesso').value('.', 'VARCHAR(16)') as Sesso,
Y.valore.query('DataNascita').value('.', 'VARCHAR(8)') as DataNascita,
Y.valore.query('ComuneNascita').value('.', 'VARCHAR(16)') as ComuneNascita,
Y.valore.query('ProvinciaNascita').value('.', 'VARCHAR(16)') as 
ProvinciaNascita
FROM @xml.nodes('/Locatore') AS X(valore)
CROSS APPLY X.valore.nodes('PersoneFisiche') AS Y(valore)



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se
0
Erland
2/10/2014 10:45:13 PM
On Monday, February 10, 2014 3:50:41 PM UTC+5:30, giancarlo....@gmail.com wrote:
> Hi everybody. My XML file is:
> 
> <Locatore>
> 
>   <NumeroProgressivo>001</NumeroProgressivo>
> 
>   <CodiceFiscale>CSTNDA69P90H523R</CodiceFiscale>
> 
>   <PersoneFisiche>
> 
> 	<Cognome>CAST</Cognome>
> 
> 	<Nome>NADIR</Nome>
> 
> 	<Sesso>F</Sesso>
> 
> 	<DataNascita>10091979</DataNascita>
> 
> 	<ComuneNascita>RONCA</ComuneNascita>
> 
> 	<ProvinciaNascita>BL</ProvinciaNascita>
> 
>   </PersoneFisiche>
> 
> </Locatore>
> 
> <Locatore>
> 
>   <NumeroProgressivo>002</NumeroProgressivo>
> 
>   <CodiceFiscale>PRSGRI74L29F443L</CodiceFiscale>
> 
>   <PersoneFisiche>
> 
> 	<Cognome>PERISSI</Cognome>
> 
> 	<Nome>IGOR</Nome>
> 
> 	<Sesso>M</Sesso>
> 
> 	<DataNascita>29071970</DataNascita>
> 
> 	<ComuneNascita>MONTE</ComuneNascita>
> 
> 	<ProvinciaNascita>SA</ProvinciaNascita>
> 
>   </PersoneFisiche>
> 
> </Locatore>
> 
> 
> 
> I need to get in ONE RECORD, both node value and his child nodes values, like this
> 
> 001 | CSTNDA69P90H523R | CAST    | NADIR | F | 10091979 | RONCA | BL
> 
> 002 | PRSGRI74L29F443L | PERISSI | IGOR  | M | 29071970 | MONTE | SA
> 
> ....
> 
> 
> 
> With this: 
> 
> SELECT 
> 
> X.valore.query('NumeroProgressivo').value('.', 'VARCHAR(20)') as NumeroProgressivo,
> 
> X.valore.query('CodiceFiscale').value('.', 'VARCHAR(16)') as CodiceFiscale
> 
> 
> 
> FROM Tbulk 
> 
> CROSS APPLY Tbulk.nodes('Fornitura/Documento/Soggetti/PrimoModulo/Locatore') AS X(valore);
> 
> 
> 
> I only get the first node values (NumeroProgressivo, CodiceFiscale), but not the child nodes (cognome, nome sesso...)


You can do it using OpenXML query that is an easy approach i think :)
0
Bal
3/11/2014 12:28:50 PM
Reply:

Similar Artilces:

select nodes with child node A and child node B
I have an XML document that has multiple childe nodes like the one at the end of this message What syntax would I use with selectNodes to select all viewentry nodes that have an entrydata child node with a name attribute equal to "CourseOrMeetingName" that has a text childe node with the text Course ABC AND that also have an entrydata child with a name attribute equal to Start Date that has a datetime child node with the text 20070110 I was trying something like //viewentry[ (entrydata[@name='CourseOrMeetingName'][text='Course ABC']) and (entrydata[@name='St...

Getting all nodes and node details from xml using xquery
Hi, I haev the following code: DECLARE @x xml SET @x=' <Root> <row id="1"><name>Larry</name><oflw>some text</oflw></row> <row id="2"><name>Joe</name></row> <row id="3" /> </Root> ' exec sp_xml_preparedocument @idoc OUTPUT, @x SELECT * FROM OPENXML(@idoc, '/Root') This gives the following details id parentid nodetype localname prefix namespaceuri datatype pr...

How to get the value of an attribute in an XML node?
Hello, This is the url http://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=gene&retmax=10000&term=cmyc of an xml file that I am trying to read and get the values of the <IdList> node in the - <IdList> <Id>30686</Id> <Id>4609</Id> <Id>17869</Id> <Id>5728</Id> <Id>6598</Id> <Id>9111</Id> </IdList> Here is the program that I am trying to read the values of the Id in the XML file ******************************program**************************** public class EUtilParseExample { private static final String urlSearch = "http://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?"; private static final String urlFetch = "http://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?"; public static void main(String[] args) { InputStream input = null; try { URL u = new URL (urlSearch + "db=gene&retmax=10000&term=cmyc"); input = u.openStream(); System.out.println(u.toString()); XMLInputFactory factory = null; System.setProperty("javax.xml.stream.XMLInputFactory", "com.bea.xml.stream.MXParserFactory"); String filename = ""; //XMLInputFactory factory = XMLInputFactory.newInstance(); factory = XMLInputFactory.newInstance(); XMLEventReader r = factory.createXMLEventReader(filename, input); //iterate as long as there are more events on the input stream while(r...

Simple XML impossible if nodes and child have values?
Is it impossible to use simple xml to parse a remote xml file with php when the external xml file got values in the elements? Consider this (almost real) XML structure: - <SOMETHING> -<Result Name="GetTheResultForLastXMinutes" Success="1"> - <Returnset> - <Here> <LoginName Type="string" Value="theloginname"/> <Country Type="string" Value="germany"/> <City Type="string" Value=""/> ...

get IsValid boolean value from XML node
I'm trying to check for a valid result from the rest request XML object in the "IsValid" item node. It seems like it should work but I'm getting no value in $result and the code gives me a result before I added this line: if ($parsed_xml->Items->Request->IsValid["True"]) ---PHP Code--- $request = file_get_contents("http://free.apisigning.com/onca/xml?". $allparameter); $parsed_xml = simplexml_load_string($request); if ($parsed_xml->Items->Request->IsValid["True"]){ $result= $parsed_xml->Items->Item->ItemAttr...

Is it better to use Invoke nodes or property nodes to set/get control values?
I have a series of VI's that run in parallel, each to manage different functions- pumping, sensing, a fluid flow model, an experiment generator/runner. These need to exchange data, which I am currently doing using invoke nodes (that are all in subVIs), using methods "Set control value" and "Get control value". I find that every now and then (perhaps 1% of the time) the data isn't exchanged correctly and therefore the system doesn't work. I can imagine how "set" could go wrong if they happen simultaneously, and can devise ways of preventing this. Howeve...

XML Node, HTML Node
hi guys, how can distinguish between XML Node and HTML Node, using attributes or method .. thanks . alhalayqa wrote: > how can distinguish between XML Node and HTML Node, using attributes or > method .. In the XML DOM nodes do not have an id property or a className property while in the HTML DOM nodes do have those properties. On the other hand there are specializations of the XML DOM like the SVG DOM where id or className are properties too. Thus if you parse XML with namespaces with browsers like Mozilla or Opera the parser recognizes namespaces like the XHTML 1 namespace o...

problem with child text node when constraining other child node types
I'm struggling with whether or not it is possible to represent the following construct in a dtd. I have an element X that I want to contain 3 types of child elements. Child Element A should have a 0 or 1 constraint, Child element B should have a 0-n constraint. I also want X to be able to contain text, resulting in xml that the following <X> <A/> <B/> <B/> child text of element X also possible </X?> I've got the following, but can't seem to figure out a way to also allow for a text child node. Any help appreciated <!ELEMENT X (A?, B*)> Bryan...

How to get value of XML "Element " type node
Hi, I want to fetch some values from a XML file, using a java program. I want to retrieve value of path tag for e.g<path>some path</path> tag.(Marked with **** in the below XML file Example) ============= My XML File ============ <maindata id="U00abfd8a-0ce7-ced5-00f6-00fff5bfdea9" host="e05k02" addr="10.64.33.86" domain="BrioPortal 7.0 Development" version="7.0.1.17" date="2003-08-04 14:20:23-07"> <Category name="MigSMACT" version="7.0.1.17" id="U01929031-0e01-ced5-00f6-00fff5bfdea9&qu...

Reality check: Is it sensible to link XML nodes to other XML nodes in the same file?
I've got a whole list of descriptions of stages in a production process, which are organised as a sequence of steps, with some branching (i.e. a sort of flow chart)... and thought I'd try and get with the times and model the data using XML! I'm hoping I'll eventually be able to display each 'step' (node) on it's own Web page, and show what link comes next. Is this a sensible thing to attempt to do? If so, how do I link to other nodes in the XML file? Should the <link> be to another node's <id>? And will each node need a 'friendly' <name...

XSLT Child node of a specific node
Hi All, I would like to use XSLT to replace all <u> nodes that are children of a <b> node with a new <heading> node. Also, if the <b> node has no other children than remove it as well. For example: This would be some <b><u>Text</u></b>. It could <b> also maybe be like <u>this</u>...</b> to This would be some <heading>Text</heading>. It could <b> also maybe be like <heading>this</heading>...</b> I've been trying something like: <xsl:template match="//b[u]"> ...

get a xml node as-it-is
i hve a xml like this:<Students> <Student name = "arun1" number="6"> <Student name = "arun2" number="7"> <Student name = "arun3" number="8"> <Student name = "arun4" number="9">............</Students>i need to build a new xml or a string from this with few studentnodes.... some thing like this..<Student> <Student name = "arun2" number="7"> <Student name = "arun3" number="8"></Student>by selecting only the two no...

get a xml node as-it-is
i hve a xml like this: <Students> <Student name = "arun1" number="6"> <Student name = "arun2" number="7"> <Student name = "arun3" number="8"> <Student name = "arun4" number="9"> .... .... .... </Students> i need to build a new xml or a string from this with few student nodes.... some thing like this.. <Student> <Student name = "arun2" number="7"> <Student name = "arun3" number="8"> </Student> by select...

Extracting the first child node of a parent node
Hei, I have the following xml file and I have tried to write xslt to extract only the values of the first "record" node. It does not work. I need some help. I used ---- to represent indent. Xml: <?xml version="1.0" encoding="UTF-8"?> <OAI-PMH xmlns="http://www.openarchives.....OAI/2.0/OAI-PMH.xsd"> ---<responseDate>2008-02-19T12:54:06Z</responseDate> ---<request xmlns="" verb="ListRecords" ......o.no</request> ---<ListRecords xmlns=""> -----<record> <!----the ...

Selecting Nodes Using Subtotal of Child Nodes
I'm having difficulty finding the correct syntax that will allow me to select a group of invoices based on the total of an amount column located in its line items. Below are simplified examples of my XML and XSLT files: XML FILE <?xml version="1.0" standalone="yes"?> <?xml-stylesheet type="text/xsl" href="OutstandingInvoiceBalances.xslt"?> <ProgramData> <Invoices> <InvoiceID>1</InvoiceID> <InvoiceNumber>100</InvoiceNumber> <Amount>1000.00</Amount> </Invoices> <I...

Inserting Nodes between Nodes
Hi, XML novice here with another question. I need to add a XML node like, [code] <child01> <child02> <text></text> <image>image 1</image> <image>image 2</image> </child02> </child01> [/code] into this node between the ending </child01> and beginning <child01> of the [code] <child01> <child02> <text></text> <image>image 1</image> <image>image 2</image> <...

selecting nodes between other nodes
Hi! <root> <foo>bar</foor> <blah /> <blah2 /> <a> <b /> <c /> </> </root> I want to select everything between <foo> and <a>. Is this possible? Timo In article <2jbjjhFvf4ttU1@uni-berlin.de>, Timo Nentwig <tcn@spamgourmet.org> wrote: % <root> % <foo>bar</foor> % <blah /> % <blah2 /> % <a> % <b /> % <c /> % </> % </root> % I want to select everything between <foo> and <a>. Is this possible? How about <xsl:styles...

How do I get the value of a text node?
Hi, If I have a TD, whose id = "myTd," that only contains text within it,how do extract that text? Thanks, - Dave laredotornado <laredotornado@zipmail.com> writes: > If I have a TD, whose id = "myTd," that only contains text within > it,how do extract that text? Find all child nodes that are text nodes, and extract their content. Even if the td only contains text, that text might be split over more than one text node (worst case). var td = document.getElementById("myTd"); var textNodeContents = []; for(var chld = td.firstChild; chld; chld = chld....

XSL select only nodes which contain a specific child node
Given the following extremely simplified XML... <AA> <BB></BB> <BB><CC>foo</CC></BB> <BB></BB> <BB><CC>bar</CC></BB> </AA> ....is there an easy way to use and XSL select to get only the <BB> nodes which have a <CC> child node? <xsl:for-each select="AA/BB"> Gives me 4 nodes: <BB></BB> <BB><CC>foo</CC></BB> <BB></BB> <BB><CC>bar</CC></BB> <xsl:for-each select="AA/BB/CC"> Give...

Select Node Using position or value of another node.
Hi, I'm trying to get the value of another node using the position of another node or the name of the tag. Such that the current node is one of the contacts child nodes sec or prim and doing <xsl:value-of select="//cust[seq = position()]/name"/> Should return jon for the prim tag and peter for the sec tag, but fails as position() appears to resolve to the position of cust not the contacts node alternatively I would like to do something like <xsl:value-of select="//cust[seq = [name()='prim' then 0 else 1]]/name"/> <custs> ...

Reordering Xml Child Nodes
I am wondering if it is possible to create a JavaScript function that will re-order child nodes in an Xml Document. Currently I am binding an Xml Data Island to an HTML table and implemented drag-and-drop functionality. Let's say I have the following: Sequence 1 - 1 - 2 - 3 Sequence 2 - 4 - 5 - 6 The user re-orders so that: Sequence 1 - 3 - 4 - 1 Sequence 2 - 5 - 6 - 2 Is it possible to sort each sequence's nodes from lowest to highest? I have read about doing this with XSLT but figured I'd see if there was an easier way. Thanks in advance, - will javascript@willasrari.c...

change the icon of a JTree parent node according to its child nodes
Dear All I am using SWING , JTree , in a software app. to monitor some hardware devices using SNMP I am organizing the devices in a JTree subclass I have a parent node with some child nodes I need to change the parent-node's icon according to some conditions in its child-nodes for example : when a child-node.getX() > 1000 then its parent-node.icon should be changed I think that I need to handle the paint of the parent-node but I don't know how ? Do I need to have subclasses of TreeNode for both Parent and Child nodes ?? I am using the MutableTreeNode for all my nodes ! Than...

Problem updating an attribute that appears in both root node and child node!
Hi I have an attribute the appears in both the root node and child node for example, below the attribute VERSION appears in the rood node (PRODMSG ) and a child node (OPERATION ) ================ INPUT XML ================ <?xml version="1.0" encoding="utf-16"?> <PRODMSG VERSION="1.2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ID="00051004_20070503024353"> <HEADER> <OPERATION NAME="PRICE" VERSION="1.2"> <USERNAME>SMIT...

node-node adjacency matrix to a node-arc adjacency matrix
I have been trying to figure out how to create a arc-node adjacency matrix from a node-node adjacency matrix, for example: node-node adj A = [0 1 0 1 1; 0 0 1 1 0; 0 0 0 1 1; 0 0 0 0 1; 0 0 0 0 0]; looking for the following links: 1-2,1-4,1-5,2-3,2-4,3-4,3-5,4-5 arc-node adj B =[ 1 1 1 0 0 0 0 0 -1 0 0 1 1 0 0 0 0 0 0 -1 0 1 1 0 0 -1 0 0 -1 -1 0 1 0 0 -1 0 0 0 -1 -1]; Any help will be greatly appreciated: I have tried to develop a for loop using "A" and assigning -1 to the tail end of the link but with no succes...

Web resources about - Get XML values from nodes and child nodes - comp.databases.ms-sqlserver

Values Voter Summit - Wikipedia, the free encyclopedia
The Values Voter Summit is an annual political conference for American social conservative activists and elected officials from across the United ...

Facebook Is No. 5 on Glassdoor’s Top 25 Companies for Culture & Values
Facebook placed fifth on the 2014 list of Top 25 Companies for Culture & Values from social jobs and career community Glassdoor , which was released ...

Glassdoor: Facebook rated No. 5 nationally in culture, values
Facebook has been highly-rated by its employees for culture and values , coming in at No. 5 overall nationally in a new Glassdoor report . Twitter ...

Gay Family Values (@Depfox) on Twitter
Sign in Sign up You are on Twitter Mobile because you are using an old version of Internet Explorer. Learn more here Gay Family Values @ Depfox ...

Common Lab Values on the App Store on iTunes
Get Common Lab Values on the App Store. See screenshots and ratings, and read customer reviews.

Tony Perkins' Remarks on Election 2012 and Values Voter Summit - YouTube
FRC's Tony Perkins to Discuss Presidential Race At National Press Club's Newsmakers Event September 23, 2011 WASHINGTON, D.C. - Family Research ...

Married Life: For parents, Ramadan is a time to think about values that are passed down
I worry that my 22-month-old baby will grow up thinking Ramadan is all about the food.

Forced marriages an 'assault on values': Scott Morrison
Immigration Minister Scott Morrison says forced marriages are an "assault on Australian values" after authorities intercepted a 14-year-old girl ...

America's values on trial with Manning
Over the past 2½ years, all of which he has spent in a military prison, much has been said about Bradley Manning, but nothing has been heard ...

Migrants must follow Australian values, says Frank Lowy
... boat person who became a shopping centre billionaire, has called on migrants to honour the ''great unwritten deal'' to follow Australian values ...

Resources last updated: 3/6/2016 7:47:02 AM