March 27, 2009 at 10:30 am
checked spelling. i must be something else. this is my first foray into xquery. cheers for the help. if you're ever in glasgow let me know i owe you a beer.
March 27, 2009 at 10:49 am
cdata is abbreviation of what the tag actually is. cheers.
March 27, 2009 at 11:05 am
enzomenoni (3/27/2009)
checked spelling. i must be something else. this is my first foray into xquery. cheers for the help. if you're ever in glasgow let me know i owe you a beer.
Glasgow? Now that's a tempting offer!
... You mean Glasgow, Scotland, right? Because I've been to Glasgow, Pennsylvania and it's not that tempting. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2009 at 11:52 am
You need to slightly modify the XQuery in the value function. I've attached a sample using your sample data.
What I've done in it is add a "/." to the end of the path to the value.
I tested what I've uploaded, and it worked.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2009 at 12:43 pm
Yep, Glasgow, Scotland. Didn't realise that America had a Glasgow.
March 29, 2009 at 12:46 pm
enzomenoni (3/29/2009)
Yep, Glasgow, Scotland. Didn't realise that America had a Glasgow.
Actually, I think that there is like six Glasgows, in different states.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2009 at 12:53 pm
I take it you guys are based in the states then?
March 29, 2009 at 1:00 pm
Hopping in late ...
I've recently had about the same issue....
http://www.sqlservercentral.com/Forums/Topic678236-21-1.aspx
JohnG and Jacob helped out well.
Jacob Sebastian's article on XML are great.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 30, 2009 at 4:42 am
Here's a more accurate copy of the xml i need to xquery.
March 30, 2009 at 7:19 am
Your XML nodes are qualified by namespaces. Therefore, you must specify the namespaces as part of your XQuery.
See Jacob Sebastian's article (one of a series on XML) here:
http://www.sqlservercentral.com/articles/XML/61333/
You can use the WITH XMLNAMESPACES clause at the beginning of your query to establish the namespace prefixes. Then you must qualify the nodes in the XQuery. In other words, the XQuery must match your XML.
See BOL: http://technet.microsoft.com/en-us/library/ms177607(SQL.90).aspx
March 30, 2009 at 8:22 am
Here's a shortcut way to deal with the namespaces problem:
SELECT CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).value('(//*:DocOrgName)[1]', 'NVarchar(50)')
, CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).query('(//*:DocOrgName/text())')
from DocumentSource
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 9:24 am
Using the sample data posted, here is a simple query extracting out the values of the GName and FamName elements.
The sample code shows how namespaces are used in the XQuery. Note that XQuery is case-sensitive!
Naturally, the namespace URIs have been obfuscated in the sample provided so that they are all the same, but you should get the idea. Note that you also have to specify a default namespace as without it, you cannot find the root (document) element as it is unqualified.
WITH XMLNAMESPACES
(
'http://www.stuff' AS "gen",
'http://www.stuff' AS "sci",
'http://www.stuff' AS "h",
'http://www.stuff' AS "xsi",
DEFAULT 'http://www.stuff'
)
SELECT
x.patient.value('(gen:GName/.) [1]', 'nvarchar(100)') AS GName,
x.patient.value('(gen:FamName/.) [1]', 'nvarchar(100)') AS FamName
FROM @XmlData.nodes('/DocumentUpload/sci:MessData/sci:PatDemog/gen:PatName/gen:StructName') AS x(patient)
March 31, 2009 at 2:52 am
Cheers guys, you have been a lot of help it's working now.
Kind regards to you all
E
March 31, 2009 at 7:22 am
Nice example, JohnG. Mind if I quote it in my Blog sometime?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 8:06 am
RBarryYoung (3/31/2009)
Nice example, JohnG. Mind if I quote it in my Blog sometime?
Sure, no problem. Glad that I could be of assistance.
Side bar (or why I had to understand XML namespaces):
We recently starting using XML as a transport mechanism (i.e., parameter values) to pass data into our stored procedures from the BLL/DAL layer of our .Net web application. We also return the data back from the stored procedure as XML vs. numerous data tables (data sets).
Note that the data, passed as XML is still "shredded" into proper relational tables.
Note that we did not store the XML. All of the XML was transient during the execution of the application. It is just a transient data store mechanism used by all layers of the application.
We did this for several reasons. The key reason being that SQL Server 2005 does not allow an array of values, i.e., an array of structures (records), to be passed to a stored procedure. Oracle has had this feature since Oracle8 (circa mid-90s). Without this capability, the stored procedure signature is all scalar parameters which makes maintenance a nightmare. Additionally, when there are multiple "records" of data to process, the application must call the stored procedure for each record. Example: a Sales Order master/detail.
Using XML, only a single parameter needs to be provided to the stored procedure. The stored procedure can use, examine, etc. only those XML elements/attributes that it wants, and can process multiple records in a single operation.
The other benefit was that the .Net application can work with the same XML as used by the stored procedure thus eliminating the constant conversion from/to/from XML to scalar parameters and data tables.
XML namespaces: The XML used by the .Net application had an associated schema (XSD) which was qualified by a "default" namespace for each (transient) document. So the individual elements were not prefixed. Note that we had multiple "documents" -- each matching the data for the "task/data at hand". Hence the need for different namespaces; e.g., each document could have a "Name" or "ID" attribute. So it became important for all parties using the XML to specify the proper namespace(s), including the "default" namespace, when processing (reading) or creating (i.e., FOR XML) the XML document.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply