|
Hi, I have a XML column (name = 'xml') in a table 'contact' in my DB which has rows as the following
<Contact xmlns="http://www.focus-solutions.co.uk/focus360/1.0" id="00496bb7-0046-4459-8cf8-002cc5921f4c" documentId="494f4671-efe8-4577-aecc-4424b699a89c">
<Type code="FirstMeeting">First meeting</Type>
<Location code="ClientHome">Client Home</Location>
<PresentationInd>true</PresentationInd>
<OtherAddress>
<HouseNameNumber>1</HouseNameNumber>
<AddressLine1>Royal Terrace Gardens</AddressLine1>
<City>Edinburgh</City>
<Postcode>EH7 5DX</Postcode>
</OtherAddress>
<Date>2009-09-08</Date>
<Time isoTime="15:00:00">
<Hours>03</Hours>
<Minutes>00</Minutes>
<Period>PM</Period>
</Time>
<Duration>
<Hours>01</Hours>
<Minutes>00</Minutes>
</Duration>
<Notes />
<LetterSentInd>true</LetterSentInd>
<Documents>
<Document id="a7b36eec-ed51-4f22-8465-267a893fd8a0" externalId="494f4671-efe8-4577-aecc-4424b699a89c" userAttached="false">
<Type code="ConfirmationLetter">Confirmation Letter</Type>
<Reference>Joe Marton First meeting</Reference>
<CreatedDate>2009-09-18</CreatedDate>
</Document>
</Documents>
<CustomQuestions>
<Question type="HasMeetingTakenPlace">
<Answer>true</Answer>
</Question>
<Question type="ClientContacted">
<Answer>Joe Marton</Answer>
</Question>
</CustomQuestions>
<Valid>true</Valid>
</Contact>
So this is just one row in the table.I am complete beginner when it comes to querying XML datatypes in a table.
I need to be able to list all records in the table CONTACT for which the the value <PresentationInd>true</PresentationInd> is true.
Help appriciated!
Thanks
|
|
|
|
|
Here is the sample for XML Data Processing..
-- Create the table.
CREATE TABLE T (
ProductID int primary key,
CatalogDescription xml)
GO
-- Insert sample data.
INSERT INTO T values(1, '<productdescription productid="1" productname="SomeName">;')
GO
SELECT ProductID,
CatalogDescription.value('(/ProductDescription/@ProductName)[1]', 'varchar(40)') AS PName
FROM T
GO
So for your requirement
SELECT COLUMNNAME.value('(/Contact/PresentationInd)[1]', 'varchar(5)') AS PresentationInd
modified on Monday, September 28, 2009 6:57 AM
|
|
|
|
|
I’m new to XSL and struggling with this for awhile. Hope someone can take a look at my code and provide me with your feedback. The output looks close to what I want, but it’s not quite there yet. I’m looking for certain attributes (id=GUIDs) in ‘task’ element and copy the entire ‘task’ element if the attribute is matched with what I’m looking for. However the namespace cannot be copied correctly. Basically I don’t want the output to have the reference “:det” in taskGroup element and xmlns=http://schemas... in task element. How do I do this? Please see the source xml, xsl, current incorrect output and expected output below. Thank you in advance!
Source XML:
<?xml version="1.0" encoding="utf-8"?>
<taskGroup revocation="false" taskGroupGuid="{a0949a60-006c-4b35-b334-97ca289b06cd}" xmlns="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks">
<tasks>
<task id="{f418cae0-6b68-409b-93f4-a19fa1a5faad}" rank="2" name="1" description="2" tooltip="2" >
<command type="ShellExecute">
<arguments>
<commandLine>www.xxxxxx.com</commandLine>
</arguments>
</command>
</task>
<task id="{91395410-c164-468c-a247-14bbd7ceea88}" rank="5" issueTime="2009-09-15T11:05:42Z" requiresElevation="false" name="3" description="4" tooltip="4" >
<command type="ShellExecute">
<arguments>
<commandLine>www.yyyyyy.com</commandLine>
</arguments>
</command>
</task>
</tasks>
</taskGroup>
...
...
...
XSL:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" xmlns:det="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks"
>
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<taskGroup revocation="false" taskGroupGuid="{{a0949a60-006c-4b35-b334-97ca289b06cd}}" >
<xsl:call-template name="taskList"></xsl:call-template>
</taskGroup>
</xsl:template>
<xsl:template name="taskList">
<xsl:copy-of select="//det:task[@id='{91395410-c164-468c-a247-14bbd7ceea88}']"/>
</xsl:template>
</xsl:stylesheet>
Current Output (incorrect):
<?xml version="1.0" encoding="utf-8"?>
<taskGroup revocation="false" taskGroupGuid="{a0949a60-006c-4b35-b334-97ca289b06cd}" xmlns:det="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks">
<task id="{91395410-c164-468c-a247-14bbd7ceea88}" rank="5" issueTime="2009-09-15T11:05:42Z" requiresElevation="false" name="3" description="4" tooltip="4" xmlns="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks">
<command type="ShellExecute">
<arguments>
<commandLine>www.yyyyyy.com</commandLine>
</arguments>
</command>
</task>
</taskGroup>
Expected XML Output:
<?xml version="1.0" encoding="utf-8"?>
<taskGroup revocation="false" taskGroupGuid="{a0949a60-006c-4b35-b334-97ca289b06cd}" xmlns="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks">
<tasks>
<task id="{91395410-c164-468c-a247-14bbd7ceea88}" rank="5" issueTime="2009-09-15T11:05:42Z" requiresElevation="false" name="3" description="4" tooltip="4" >
<command type="ShellExecute">
<arguments>
<commandLine>www.yyyyyy.com</commandLine>
</arguments>
</command>
</task>
</tasks>
</taskGroup>
|
|
|
|
|
You have to have the namespace declaration somewhere/somehow - the task element in the output has been copied verbatim from the input, where it lived in the det/http://schemas.microsoft.com/windows/2008/deviceExperienceTasks namespace.
What output do you WANT to see?
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
I used a namespace reference ":det" in the xls ("xmlns:det="http://schemas.microsoft.com/windows/2008/deviceExperienceTasks"), but I want to see the namespace in the output without the reference. There is also extra namespace appears in the end of the 'task' element which I don't want.
|
|
|
|
|
Why does it make any difference? Semantically, both forms are exactly the same.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
Hi, I've got a XML schema which is split to multiple XSD files. Now we need to merge them into one big XSD file. Anyone knows a good tools I can use to do it? Manually merging would be too cumbersome, and error-prone.
Thanks very much.
|
|
|
|
|
|
I don't think this include tag will solve my problem. What I'm facing is that
- Case 1
I've got WSDL which reference multiple xsd files, I use soapUI to generate Web service, but soapUI cannot handle those multiple xsd references because the refrence URLs in all files are still referencing to web sites which are not available, unless I manually hack those URLs to reference local xsd files. But this is tedious, each time I have to do it when WSDL changes. So I need a tools to put all XSD file's content inside the WSDL file. So soapUI will accept it.
- Case 2
I also initially had a big XSD file, but client asked to split a particular path in the main XSD to a seperate XSD file, and removed this from the main XSD then, for example.
the initial Main XSD's struct:
-root node - 1st level node 1 - 2nd level node 1
- 2nd level node 2
- 1st level node 2 - 2nd level node 3
- 2nd level node 4
- 2nd level node 5
now, after split, it become two xsd files:
-root node - 1st level node 1 - 2nd level node 1
- 2nd level node 2
- 1st level node 2 - 2nd level node 5
-root node - 1st level node 2 - 2nd level node 3
- 2nd level node 4
the problem is that I use .net xsd.exe tool to generate classes from those xsd files. After splitting to 2 xsd files which are compensatory, when I use xsd.exe to generate classes again, it will get conflict classes for the root level and 1st level
so I need a tool to merge these XSD files into a single one again for generating classes.
Any ideas?
|
|
|
|
|
Did you get any tool, if you have any solution please share with me. i am also facing similar problem.
Thanks in advance
|
|
|
|
|
Also eager to hear any solution to this..
|
|
|
|
|
It's extremely unfortunate.
Obviously, there is severe lack of such a wonderful tool
I wish XML Spy could provide this kind of feature ...
|
|
|
|
|
I am a newbie to xml data manipulation.
I have Pocket PC Creations software and have created an inspection application.
I can export in XML, HTML, Excel as well. (here is a link to the product specifics):
http://www.pocketpccreations.com/skills/lecture.asp?CourseID=4&LectureID=4&Page=7
I am trying to figure out a way to create a paragraph word report with the information gathered by the pocket pc.
Is it possible to incorporate the data from the exported data into an MS Word document? If it is, can you point me in a direction for help?
Jimdandy1956
|
|
|
|
|
I am reading in some XML using the below code. The problem is when i attempt to get out the values for each Item, i get the same values again, ie: Debug prints out
Code = A123
Quantity = 10
---
Code = A123
Quantity = 10
using (TextReader textReader = new StringReader(xml))
using (XmlReader xmlReader = new XmlTextReader(textReader))
{
XPathDocument xPathDocument = new XPathDocument(xmlReader);
XPathNavigator xPathNavigator = xPathDocument.CreateNavigator();
XmlNamespaceManager xmlNamespaceManager = new XmlNamespaceManager(xPathNavigator.NameTable);
xmlNamespaceManager.AddNamespace("ns", "OrderSchema");
XPathExpression xPathExpressionItems = xPathNavigator.Compile("//ns:Order//ns:Items");
xPathExpressionItems.SetContext(xmlNamespaceManager);
XPathNodeIterator xPathNodeIteratorItems = xPathNavigator.Select(xPathExpressionItems);
do
{
string code = xPathNodeIteratorItems.Current.SelectSingleNode("//ns:Code", xmlNamespaceManager).ToString();
string quantity = xPathNodeIteratorItems.Current.SelectSingleNode("//ns:Quantity", xmlNamespaceManager).ToString();
System.Diagnostics.Debug.WriteLine("code == " + code);
System.Diagnostics.Debug.WriteLine("quantity == " + quantity);
System.Diagnostics.Debug.WriteLine("----");
}
while (xPathNodeIteratorItems.MoveNext());
}
Heres the xml i am reading in:
<?xml version="1.0" encoding="UTF-8" ?>
<Order>
<Items>
<Item>
<Code>A123</Code>
<Quantity>10</Quantity>
</Item>
<Item>
<Code>A456</Code>
<Quantity>5</Quantity>
</Item>
</Items>
</Order>
Any ideas what i am doing wrong?
Regards,
Gareth.
(FKA gareth111)
|
|
|
|
|
The only thing that jumps out is that you use Current before calling MoveNext the first time - the documentation[^] says you need to call MoveNext before Current.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
Hi Gareth-
Please check this method:
private void ReadXMLData()
{
XmlDocument xDoc=new XmlDocument();
xDoc.LoadXml("<?xml version=\"1.0\" encoding=\"UTF-8\" ?><Order><Items><Item><Code>A123</Code><Quantity>10</Quantity></Item><Item><Code>A456</Code><Quantity>5</Quantity></Item></Items></Order>");
XmlNodeList xNodeList = xDoc.GetElementsByTagName("Item");
for (Int16 iCount = 0; iCount < xNodeList.Count; iCount++)
{
Response.Write(xNodeList[iCount].ChildNodes.Item(0).InnerText+ "<br/>");
Response.Write(xNodeList[iCount].ChildNodes.Item(1).InnerText + "<br/>");
Response.Write("-----<br/>");
}
}
|
|
|
|
|
I've created the following XML Schema (not for production use, but to learn by doing).
Here's what is burning me at the moment:
The xs:element elements that refer to classes or properties are giving me warnings that "Type 'class' is not declared". I'm still learning this stuff, so I'd really appreciate it if someone could take a quick peek at it and advise me of the (probably obvious) problem in the code below.
Thanks,
Will
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://gantsoftwaresystems.com/classspec.xsd"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
>
<xs:element name="classDefs">
<xs:complexType>
<xs:sequence minOccurs="1" maxOccurs="unbounded">
<xs:element name="classes">
<xs:complexType>
<xs:sequence minOccurs="1" maxOccurs="unbounded">
<xs:element name="class" type="class"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="views">
<xs:complexType>
<xs:sequence>
<xs:element name="view" type="class"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="class">
<xs:sequence minOccurs="1" maxOccurs="unbounded">
<xs:element name="property" type="property"/>
</xs:sequence>
<xs:attribute name="name" type="xs:string"/>
<xs:attribute name="base" type="xs:string"/>
<xs:attribute name="hasImageUrl" type="xs:boolean"/>
</xs:complexType>
<xs:complexType name="property">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element name="alsoTriggerPropertyChanged">
<xs:complexType>
<xs:attribute name="propertyName" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="name" type="xs:string"/>
<xs:attribute name="type" type="xs:string"/>
<xs:attribute name="changesImageUrl" type="xs:boolean"/>
<xs:attribute name="imageUrlQStringParamName" type="xs:string"/>
<xs:attribute name="imageUrlOverridePropertyName" type="xs:string"/>
</xs:complexType>
</xs:schema>
|
|
|
|
|
You need to namespace qualify the type references - you declare them in your target namespace, but when you reference them, don't mention that namespace. You can either use explicit namespace references or (and this is easier) use your target namespace as the default namespace for the schema.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
i need some help on xml. i want to send an xml document to an xml web service using get method. but i didn't find any article, helping material. i did not use xml before. please help me.
thanx
amirzada
|
|
|
|
|
|
You want to 'send' an XML payload with a GET…an amusing concept. Look at the verbs we're using SEND, GET - they're not compatible. You want to use a POST method to send an XML document to a server.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
thanx for your help but now i get an exception when i try create an xml document it show that the body and html tag is not closed how can i handle can you help me please.
thanx
amirzada
|
|
|
|
|
More detail - where does the HTML come in - that's NOT XML.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
I have a tool called "Flat file to XML converter" to convert the text file information to XML file using the XSD template available. Text file contains employee details. Whenever a name with apostrphe(') comes the tool converts it as ' instead of '. So whenever the XML file is decoded to view, it is showing the name with '. Please help me in this.
To make any changes in the tool is not possible and no XSLT is available for the XML file. Only XSD is there. I need to validate this ' and replace it with ' Is it possible?
Only a change in XSD is allowed. No seperate application/tool to convert the XML is allowed.
Example:-
flat fle contains the name- O'NEIL, JOHN.
XML file after conversion - O 'NEIL, JOHN.
Output needed - O'NEIL, JOHN
|
|
|
|
|
Validation DOES NOT alter the input XML - it just tells you if it meets the Schema or not.
Why does it matter, anyway? Any conforming XML reader will convert the ' entity to an apostrophe when it reads it!
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|