September 18, 2014 at 3:11 pm
I have an XML file that I've stored in a single field of a table with an XML data type. I want to query values out of the file; however, it always comes back Blank. 2 be specific, I'm trying to query the value 2014048700 from the XML string.
Here is the table structure:
USE [GalvestonTX]
GO
/****** Object: Table [dbo].[XML_Convert] Script Date: 09/18/2014 16:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XML_Convert](
[Kachunk] [xml] NULL
) ON [PRIMARY]
GO
Here is the XML string that is in the single field of the table:
<SEARCHREQUESTRESULT><DOCUMENTS>
<DOCUMENTDETAIL>
<DOCUMENTDATE/>
<CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT>
<BOOK/>
<PAGE/>
<REFERENCENUMBER/>
<NOTES/>
<RETURNEDTO/>
<PARTIES>
<PARTY>
<ID>1090</ID>
<NAME>MORTGAGE ELECTRONIC REGISTRATION SYSTEM</NAME>
<ADDITIONALNAME/>
<PARTYTYPEID>1</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
<PARTY>
<ID>1091</ID>
<NAME>GATES</NAME>
<ADDITIONALNAME>DANIEL</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
<PARTY>
<ID>1092</ID>
<NAME>GATES</NAME>
<ADDITIONALNAME>KIMBERLY</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
</PARTIES>
<LEGALS>
<LEGALBASE>
<ID>65</ID>
<DESCRIPTION>344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573</DESCRIPTION>
<NOTES/>
<PROPERTYNOTES/>
<LEGALTYPE>A</LEGALTYPE>
<SESSIONID>0</SESSIONID>
</LEGALBASE>
</LEGALS>
<LEGALDETAILS>
<ADDRESSLEGAL>
<HOUSENUMBER>344</HOUSENUMBER>
<STREET>BRANDY RIDGE LANE</STREET>
<STREETDIRECTION/>
<CITY>LEAGUE CITY</CITY>
<STATE>TX</STATE>
<ZIPCODE>77573</ZIPCODE>
<ID>65</ID>
<DESCRIPTION>344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573</DESCRIPTION>
<NOTES/>
<PROPERTYNOTES/>
<LEGALTYPE>A</LEGALTYPE>
<SESSIONID>0</SESSIONID>
</ADDRESSLEGAL>
</LEGALDETAILS>
<FEES/>
<ASSOCIATEDDOCUMENTS>
<ASSOCIATEDDOCUMENT>
<ID>0</ID>
<NAME>2007044874</NAME>
<DOCSTATE/>
<RECORDEDDATETIME/>
<TYPE>
<DOCUMENTTYPE>
<ID>0</ID>
<CODE/>
<NAME>RELEASE</NAME>
<SESSIONID>0</SESSIONID>
</DOCUMENTTYPE>
</TYPE>
<PARTY1/>
<ERLEVEL/>
<SOURCE/>
<PARTY2/>
<NUMBEROFPAGES>0</NUMBEROFPAGES>
<SESSIONID>0</SESSIONID>
</ASSOCIATEDDOCUMENT>
</ASSOCIATEDDOCUMENTS>
<ID>834</ID>
<NAME>2014048700</NAME>
<DOCSTATE>A</DOCSTATE>
<RECORDEDDATETIME>8/27/2014 12:07:11 PM</RECORDEDDATETIME>
<TYPE>
<DOCUMENTTYPE>
<ID>577</ID>
<CODE>RELEASE</CODE>
<NAME>RELEASE</NAME>
<SESSIONID>0</SESSIONID>
</DOCUMENTTYPE>
</TYPE>
<PARTY1>MORTGAGE ELECTRONIC REGISTRATION SYSTEM</PARTY1>
<ERLEVEL/>
<SOURCE/>
<PARTY2>GATES, DANIEL</PARTY2>
<NUMBEROFPAGES>3</NUMBEROFPAGES>
<SESSIONID>0</SESSIONID>
</DOCUMENTDETAIL>
</DOCUMENTS></SEARCHREQUESTRESULT>
Here is the script that I'm tinkering with:
Declare @vXML XML
Select @vXML = Kachunk
From XML_Convert
Print convert(Varchar(8000),@vXML,1)
Select @vXML.query('/DocumentDetail/Name/text()')
Any help is greatly appreciated....
Thanks
Crusty
September 18, 2014 at 3:39 pm
Here's what worked for me:-
CREATE TABLE [dbo].[XML_Convert](
[Kachunk] [xml] NULL
) ON [PRIMARY]
GO
DECLARE @Text XML
SET @Text =
'<SEARCHREQUESTRESULT><DOCUMENTS>
<DOCUMENTDETAIL>
<DOCUMENTDATE/>
<CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT>
<BOOK/>
<PAGE/>
<REFERENCENUMBER/>
<NOTES/>
<RETURNEDTO/>
<PARTIES>
<PARTY>
<ID>1090</ID>
<NAME>MORTGAGE ELECTRONIC REGISTRATION SYSTEM</NAME>
<ADDITIONALNAME/>
<PARTYTYPEID>1</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
<PARTY>
<ID>1091</ID>
<NAME>GATES</NAME>
<ADDITIONALNAME>DANIEL</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
<PARTY>
<ID>1092</ID>
<NAME>GATES</NAME>
<ADDITIONALNAME>KIMBERLY</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
<TYPE/>
<ADDRESS1/>
<ADDRESS2/>
<CITY/>
<STATE/>
<ZIPCODE/>
<ATTRIBUTE/>
<SESSIONID>0</SESSIONID>
</PARTY>
</PARTIES>
<LEGALS>
<LEGALBASE>
<ID>65</ID>
<DESCRIPTION>344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573</DESCRIPTION>
<NOTES/>
<PROPERTYNOTES/>
<LEGALTYPE>A</LEGALTYPE>
<SESSIONID>0</SESSIONID>
</LEGALBASE>
</LEGALS>
<LEGALDETAILS>
<ADDRESSLEGAL>
<HOUSENUMBER>344</HOUSENUMBER>
<STREET>BRANDY RIDGE LANE</STREET>
<STREETDIRECTION/>
<CITY>LEAGUE CITY</CITY>
<STATE>TX</STATE>
<ZIPCODE>77573</ZIPCODE>
<ID>65</ID>
<DESCRIPTION>344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573</DESCRIPTION>
<NOTES/>
<PROPERTYNOTES/>
<LEGALTYPE>A</LEGALTYPE>
<SESSIONID>0</SESSIONID>
</ADDRESSLEGAL>
</LEGALDETAILS>
<FEES/>
<ASSOCIATEDDOCUMENTS>
<ASSOCIATEDDOCUMENT>
<ID>0</ID>
<NAME>2007044874</NAME>
<DOCSTATE/>
<RECORDEDDATETIME/>
<TYPE>
<DOCUMENTTYPE>
<ID>0</ID>
<CODE/>
<NAME>RELEASE</NAME>
<SESSIONID>0</SESSIONID>
</DOCUMENTTYPE>
</TYPE>
<PARTY1/>
<ERLEVEL/>
<SOURCE/>
<PARTY2/>
<NUMBEROFPAGES>0</NUMBEROFPAGES>
<SESSIONID>0</SESSIONID>
</ASSOCIATEDDOCUMENT>
</ASSOCIATEDDOCUMENTS>
<ID>834</ID>
<NAME>2014048700</NAME>
<DOCSTATE>A</DOCSTATE>
<RECORDEDDATETIME>8/27/2014 12:07:11 PM</RECORDEDDATETIME>
<TYPE>
<DOCUMENTTYPE>
<ID>577</ID>
<CODE>RELEASE</CODE>
<NAME>RELEASE</NAME>
<SESSIONID>0</SESSIONID>
</DOCUMENTTYPE>
</TYPE>
<PARTY1>MORTGAGE ELECTRONIC REGISTRATION SYSTEM</PARTY1>
<ERLEVEL/>
<SOURCE/>
<PARTY2>GATES, DANIEL</PARTY2>
<NUMBEROFPAGES>3</NUMBEROFPAGES>
<SESSIONID>0</SESSIONID>
</DOCUMENTDETAIL>
</DOCUMENTS></SEARCHREQUESTRESULT>'
INSERT INTO [dbo].[XML_Convert]
SELECT @Text
SELECT
Kachunk.query('(/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/ASSOCIATEDDOCUMENTS/ASSOCIATEDDOCUMENT/NAME)[1]')
FROM
[dbo].[XML_Convert]
September 19, 2014 at 6:57 am
This is AWESOME... I wasn't drilling down far enough is what you're basically showing me... BUT... Here's what I don't get....
There are 3 parties in this string... could be 10 in the next one, could be 1. Can't I query the XML in some manner to just get them all? instead of having to guess like I did in the below script?
SELECT
Kachunk.query('(/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY/NAME/text())') Party_1,
Kachunk.query('(/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY/NAME)[2]') Party_2
FROM
[dbo].[XML_Convert]
Absolutely awesome... did I mention it's awesome?
September 19, 2014 at 7:28 am
No worries! Completely agree, having to specify where in the XML the value comes is a bit of a pain.
I'm not sure of you can pull out all the values from the XML to be honest, pushing my knowledge of XQuery really.
I'll have a look a bit later but for now here's the link to the XQuery language reference MSDN site:-
September 19, 2014 at 8:48 am
Here's what I came up with...
SELECT
Kachunk.query('
for $NAME in /SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY/NAME
return string(concat($NAME,''|''))
') Party_2
FROM
[dbo].[XML_Convert]
In that same node, there is a Name and an Additional Name... now the next trick will be to concat those together BEFORE it loops to the next iteration.
September 26, 2014 at 8:04 am
Sorry, I haven't had a chance to look at this.
Did you get the query sorted in the end?
September 26, 2014 at 8:06 am
very much so.... very very much so...
Thanks for asking....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply