December 22, 2009 at 3:57 pm
Hello,
I'm trying to pull the values off an XML column and I can't seems to find why I can't.
I read the posts in the forum but none of the solutions work for me.
I'm trying to pull the data from:
ServiceResponse/Content/any0/TitleSearchProductResponse/GIPropertyTaxSearchResponses/GIPropertyTaxSearchResponse/GIChainResults/GIChainResult/GIChain/Documents/Document
but with no sucess. I think it has something to do with the structure of the document
I appreciate any help,
Thanks,
Itzhak
December 22, 2009 at 5:35 pm
It would help if you provided information about what you have tried....
however this might steer you in the right direction:
;with xmlnamespaces(
DEFAULT 'http://www.lpsvcs.com/DataStream',
'http://www.lpsvcs.com/DataStream/DataDictionary/Document' as ns24
)
select @x.query('//ns24:Document')
This does not address the Document node exactly as you require it, but it should give you enough of a pointer to do it yourself.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 22, 2009 at 6:06 pm
Thank you for your response,
What I'm trying to do is to shred the XML column I need to pull a few of the fields from the documents.
I know that my issue is with the name spaces, I stripped the top part of the document and removed the name spaces and than I'm able to query the document.
here is what i query once I removed the namespaces:
Select distinct T.c.value('(./DocumentFullName)[1]','nvarchar(50)') as FullDocument,
T.c.value('(./FilingDate)[1]','nvarchar(50)') as FilingDate,
T.c.value('(./Remark)[1]','nvarchar(50)') as Remark,
Y.d.value('(./ServiceId)[1]','int') as ServiceId
from XMLTest2
CROSS APPLY
XMLCol.nodes('/GIPropertyTaxSearchResponses/GIPropertyTaxSearchResponse/GIChainResults/GIChainResult/GIChain/Documents/Document') as T(c)
CROSS APPLY
XMLCol.nodes('/GIPropertyTaxSearchResponses/GIPropertyTaxSearchResponse/GIChainResults/GIChainResult') as Y(d)
Results from 3 documents:
FullDocumentFilingDateRemarkServiceId
JUDGMENT11/04/1975SC 126088 31707148206489
JUDGMENT06/26/1987SC 552561 24986,SAN JACINTO148206489
BANKRUPTCY08/10/1978LASAROW,5845 BURNETTE/VN 552 64 4156148206489
Thanks,
Itzhak
December 22, 2009 at 6:31 pm
I am not an expert on XML Namespaces in SQL server, so I could be off the mark, but it seems like there is an issue with the nodes that specify the empty namespace (xmlns="") , for example the GIPropertyTaxSearchResponses node.
However, if you can get away with generalising a bit, this works:
;with xmlnamespaces(
'http://www.lpsvcs.com/DataStream/DataDictionary/Document' as ns24
)
select T.c.value('(./DocumentFullName)[1]','nvarchar(50)') as FullDocument,
T.c.value('(./FilingDate)[1]','nvarchar(50)') as FilingDate,
T.c.value('(./Remark)[1]','nvarchar(50)') as Remark,
Y.c.value('(ServiceId)[1]','int') as ServiceId
from (select 1 as a ) b
cross apply @x.nodes('//ns24:Document') T(c)
cross apply @x.nodes('//GIChainResult') Y(c)
I have removed the DEFAULT namespace, which then allows us to select the GIChainResult node.
If you can guarantee those queries will get you what you want, then ok, otherwise you need to figure out how to query the GIChainResult using a full path when you have empty namespaces defined on parent nodes.
(Sorry I don't have XMLTest2, so just used (select 1 as a) as my base and an xml variable @x to contain the sample data.)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 22, 2009 at 6:51 pm
I just tried it on my data and it returned some data.
I need to play with this until I i'll get all the fields and the data I need but this certainly gave me at least a way to query the data.
Thank you very much !!!!
Itzhak
December 23, 2009 at 1:37 pm
Hello mister.magoo,
Thanks again for your help.
I managed to get the data I needed, I had an issue where the documet spacename showed up else where but I found the solution with what you showed me.
Here is the query: (the file I posted was a smaller version of the actual document):
;with xmlnamespaces(
'http://www.lpsvcs.com/DataStream/DataDictionary/Document' as ns24,
'http://www.lpsvcs.com/DataStream/DataDictionary/GIChain' as ns26,
'http://www.lpsvcs.com/DataStream/DataDictionary/DocumentParty' as ns15,
'http://www.lpsvcs.com/DataStream/DataDictionary/Image' as ns11
)
Select distinct
T.c.value('(./Marked)[1]','bit') as Marked,
T.c.value('(./Pull)[1]','bit') as Pull,
T.c.value('(./SequenceNumber)[1]','int') as SequenceNumber,
T.c.value('(./FilingDate)[1]','nvarchar(50)') as FilingDate,
T.c.value('(./ns11:Image/DocInfo)[1]','nvarchar(250)') as DocInfo,
T.c.value('(./CouplingIndicatorAll)[1]','int') as CI,
T.c.value('(./DocumentType)[1]','nvarchar(50)') as DocumentType,
T.c.value('(./DocumentClass)[1]','nvarchar(50)') as DocumentClass,
T.c.value('(./Icon)[1]','nvarchar(50)') as Icon,
T.c.value('(./Parties/ns15:DocumentParty/Name)[1]','nvarchar(50)') as Name,
T.c.value('(./Remark)[1]','nvarchar(50)') as Remarks,
T.c.value('(./RepositoryId)[1]','nvarchar(50)') as Image
--T.c.value('(./Parcels/LoanAmount)[1]','nvarchar(50)') as Amount,
from XMLTest
CROSS APPLY
SampleXML.nodes('//ns26:GIChain/Documents/ns24:Document') as T(c)
Order by 3
Thanks,
Itzhak
December 23, 2009 at 5:20 pm
Glad you got it working.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply