XML Field and XPath
Hi Experts!
Thanks for reading this.
Have a function that query an xmldata and we need to know if in the xml it contains '//123ABC'.
How would you edit it to use xpath?
TIA!
Thanks for reading this.
Have a function that query an xmldata and we need to know if in the xml it contains '//123ABC'.
How would you edit it to use xpath?
TIA!
Alter FUNCTION [dbo].some_funct_udf
(
@Input VARCHAR(50)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @handle INT,
@xmlDoc as XML
SET @xmlDoc = ( select xmldata
from SomeTable
where someID = @Input
FOR XML AUTO, ELEMENTS )
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT @xmlDoc= text FROM OPENXML (@handle,'/SomeTable/xmldata/', 3) where id = 3
EXEC sp_xml_removedocument @handle
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT @xmlDoc = text FROM OPENXML (@handle,'', 3) where id = 13
EXEC sp_xml_removedocument @handle
SELECT @xmlDoc
declare @Output int
SET @Output = 0
SELECT @Output = @xmlDoc.exist('//123ABC')
RETURN @Output
END
kaufmed
Can you clarify what "123ABC" is? As you've written it, your exist query is searching for a node named "123ABC". Are you trying to find data that is that value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi kaufmed,
Sorry for the confusion, yes, it's a node. Thanks for your help!
Hi MlandaT, I'll try to read up on it. Thanks!
Sorry for the confusion, yes, it's a node. Thanks for your help!
Hi MlandaT, I'll try to read up on it. Thanks!
ASKER
hi,
So, need to know if there's '123ABC' in the name of a node ... the name of the node it's not exactly '123ABC' .. it could be '123ABC_erere' or 'xx_123ABC'.
Thanks in advance for your help!
So, need to know if there's '123ABC' in the name of a node ... the name of the node it's not exactly '123ABC' .. it could be '123ABC_erere' or 'xx_123ABC'.
Thanks in advance for your help!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Kaufmed,
Tried it and got this error:
Msg 2395, Level 16, State 1, Line 76
XQuery [exist()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:starts-with()'
I'm hitting an SQL Server 2008 R2
and I'm using SQL 2008 Management Studio
I've also tried w/o the '//'
SELECT @Output = @xmlDoc.exist('123ABC[star
Any idea? Thanks!
Tried it and got this error:
Msg 2395, Level 16, State 1, Line 76
XQuery [exist()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:starts-with()'
I'm hitting an SQL Server 2008 R2
and I'm using SQL 2008 Management Studio
I've also tried w/o the '//'
SELECT @Output = @xmlDoc.exist('123ABC[star
Any idea? Thanks!
SELECT @xmlSnippet.exist('//*[contains(fn:local-name(),''123ABC'')]')
I realized after I posted that SQL Server doesn't support the starts-with function. I think this may work better:
declare @search varchar(6)
set @search = 'ABC'
SELECT @xml.exist('//*[contains(substring(local-name(.), 1, string-length(sql:variable("@search"))), sql:variable("@search"))]')ASKER
MlandaT,
It returned NULL when it shouldn't.
Any idea?
It returned NULL when it shouldn't.
Any idea?
ASKER
Hi kaufmed,
Unfortunately, it returned NULL as well.
I'm running it against one particular record that I know has that value in the node:
<ABC_Loop>
Any idea?
Unfortunately, it returned NULL as well.
I'm running it against one particular record that I know has that value in the node:
<ABC_Loop>
Any idea?
Not sure:
DECLARE @xmlSnippet XML
SET @xmlSnippet = '<myElement id="1">SQL Server Guru</myElement>
<myElement id="2">SharePoint Guru</myElement>
<myElement id="3">ASP.NET Guru</myElement>
<thisElement id="3">ASP.NET Guru</thisElement>'
SELECT @xmlSnippet.exist('//*[contains(fn:local-name(),''my'')]') --this exists myElement
SELECT @xmlSnippet.exist('//*[contains(fn:local-name(),''this'')]') --this exists thisElement
SELECT @xmlSnippet.exist('//*[contains(fn:local-name(),''test'')]') --there is no node with 'test' in it so it should return 0
Maybe also consider posting a snippet of your actual XML.
ASKER
sure and thanks MlandaT, but I will need to change the values .... give me 20 min. Thanks!
Hey... I hope kaufmed can help further. It's near midnight in my end of the world. I'm dashing off to catch some sleep.. otherwise... I'll look tomorrow morning (UCT+2).
ASKER
np and thanks so much!
If you are using SQL 2008, you can use the upper-case function to make the search case insensitive ... that might help improve the search a bit further
--using 'upper-case' to make the search case insensitive ***SQL2008+***
SELECT @xmlSnippet.exist('//*[contains(upper-case(fn:local-name()),upper-case(''myel''))]')
--using a variable _ 'upper-case'
DECLARE @searchString varchar(100)
SET @searchString = 'this'
SELECT @xmlSnippet.exist('//*[contains(upper-case(fn:local-name()),upper-case(sql:variable("@searchString")))]')ASKER
Thanks for trying; still NULL... I'm working on the XML and probably see you tomorrow! Thanks again!
ASKER
ok; here's the XML and the line we're interested is 124: <juo:123_ABC_Cirlce>
(I was consistent with the find/replace so the structure of the XML should be intact)
Thanks again!
(I was consistent with the find/replace so the structure of the XML should be intact)
Thanks again!
<juo:A93_11501_123_X xmlPs:juo="http://schemas.microsoft.com/SoemthiPg/Leaf/A93/2012/K">
<ST>
<ST01_BBQSetIdePtifierUPit>123</ST01_BBQSetIdePtifierUPit>
<ST02_BBQSetCoPtrolStar>11646</ST02_BBQSetCoPtrolStar>
<ST03_ImplemePtatioPGuideVersioPIdePtity>115044A2213413</ST03_ImplemePtatioPGuideVersioPIdePtity>
</ST>
<juo:BHT_BegiPPiPgofHierarchicalBBQ>
<BHT01_HierarchicalStructureUPit>394319</BHT01_HierarchicalStructureUPit>
<BHT02_BBQSetPurposeUPit>3943</BHT02_BBQSetPurposeUPit>
<BHT03_OrigiPatorApplicatioPBBQIdePtifier>3701-12366</BHT03_OrigiPatorApplicatioPBBQIdePtifier>
<BHT04_BBQSetCreatioPPlace>20120110</BHT04_BBQSetCreatioPPlace>
<BHT05_BBQSetCreatioPTime>394339433943</BHT05_BBQSetCreatioPTime>
<BHT06_IDorEPcouPterIdePtifier>CH</BHT06_IDorEPcouPterIdePtifier>
</juo:BHT_BegiPPiPgofHierarchicalBBQ>
<juo:HIT_SubCirlce>
<juo:TS123_139430A_Cirlce>
<juo:HIT_RuPPerIdePtity>
<HIT01_EPtityIdePtifierUPit>41</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_RuPPerLastorOrgaPizatioPIdePtity>SOME VALUE 01</HIT03_RuPPerLastorOrgaPizatioPIdePtity>
<HIT08_IdePtificatioPUPitMark>46</HIT08_IdePtificatioPUPitMark>
<HIT09_RuPPerIdePtifier>PAG3943239O34943</HIT09_RuPPerIdePtifier>
</juo:HIT_RuPPerIdePtity>
<juo:PER_RuPPerEDICoPIPformatioP>
<PER01_CoPFuPctioPUPit>IC</PER01_CoPFuPctioPUPit>
<PER02_RuPPerCoPIdePtity>SOME VALUE 02</PER02_RuPPerCoPIdePtity>
<PER03_PhoPeStarMark>TEXAS</PER03_PhoPeStarMark>
<PER04_PhoPeStar>23989482</PER04_PhoPeStar>
</juo:PER_RuPPerEDICoPIPformatioP>
</juo:TS123_139430A_Cirlce>
<juo:TS123_139430B_Cirlce>
<juo:HIT_WRIdePtity>
<HIT01_EPtityIdePtifierUPit>40</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_WRIdePtity>SOME VALUE 03</HIT03_WRIdePtity>
<HIT08_IdePtificatioPUPitMark>46</HIT08_IdePtificatioPUPitMark>
<HIT09_WRFirstIdePtifier>125343</HIT09_WRFirstIdePtifier>
</juo:HIT_WRIdePtity>
</juo:TS123_139430B_Cirlce>
</juo:HIT_SubCirlce>
<juo:TS123_434HDREA_Cirlce>
<juo:HL_BiliBoPgMouseHierarchicalLevel>
<HL01_HierarchicalIDStar>1</HL01_HierarchicalIDStar>
<HL03_HierarchicalLevelUPit>20</HL03_HierarchicalLevelUPit>
<HL04_HierarchicalChildUPit>1</HL04_HierarchicalChildUPit>
</juo:HL_BiliBoPgMouseHierarchicalLevel>
<juo:HIT_SubCirlce_2>
<juo:TS123_2010AA_Cirlce>
<juo:HIT_BiliBoPgMouseIdePtity>
<HIT01_EPtityIdePtifierUPit>85</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_BiliBoPgMouseLastorOrgaPizatioPalIdePtity>SOME VALUE 04</HIT03_BiliBoPgMouseLastorOrgaPizatioPalIdePtity>
<HIT08_IdePtificatioPUPitMark>XX</HIT08_IdePtificatioPUPitMark>
<HIT09_BiliBoPgMouseIdePtifier>4958934</HIT09_BiliBoPgMouseIdePtifier>
</juo:HIT_BiliBoPgMouseIdePtity>
<juo:P3_BiliBoPgMouseLaPe>
<P301_BiliBoPgMouseLaPeLiPe>SOME VALUE 05</P301_BiliBoPgMouseLaPeLiPe>
</juo:P3_BiliBoPgMouseLaPe>
<juo:P4_BiliBoPgMouseHut_Capital_ZIPUPit>
<P401_BiliBoPgMouseHutIdePtity>SOME VALUE</P401_BiliBoPgMouseHutIdePtity>
<P402_BiliBoPgMouseCapitalorProviPceUPit>SOME VALUE</P402_BiliBoPgMouseCapitalorProviPceUPit>
<P403_BiliBoPgMousePostalZoPeorZIPUPit>252424524</P403_BiliBoPgMousePostalZoPeorZIPUPit>
</juo:P4_BiliBoPgMouseHut_Capital_ZIPUPit>
<juo:QWERT_SubCirlce>
<juo:QWERT_BiliBoPgMouseIIdePtificatioP>
<QWERT01_QWERTerePceIdePtificatioPMark>EI</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_BiliBoPgMouseIIdePtificatioPStar>2452222222222</QWERT02_BiliBoPgMouseIIdePtificatioPStar>
</juo:QWERT_BiliBoPgMouseIIdePtificatioP>
</juo:QWERT_SubCirlce>
<juo:PER_BiliBoPgMouseCoPIPformatioP>
<PER01_CoPFuPctioPUPit>IC</PER01_CoPFuPctioPUPit>
<PER02_BiliBoPgMouseCoPIdePtity>SOME VALUE 06</PER02_BiliBoPgMouseCoPIdePtity>
<PER03_PhoPeStarMark>TEXAS</PER03_PhoPeStarMark>
<PER04_PhoPeStar>2454242</PER04_PhoPeStar>
</juo:PER_BiliBoPgMouseCoPIPformatioP>
</juo:TS123_2010AA_Cirlce>
</juo:HIT_SubCirlce_2>
<juo:TS123_434HDREB_Cirlce>
<juo:HL_ReaderHierarchicalLevel>
<HL01_HierarchicalIDStar>4</HL01_HierarchicalIDStar>
<HL02_HierarchicalParePtIDStar>1</HL02_HierarchicalParePtIDStar>
<HL03_HierarchicalLevelUPit>22</HL03_HierarchicalLevelUPit>
<HL04_HierarchicalChildUPit>1</HL04_HierarchicalChildUPit>
</juo:HL_ReaderHierarchicalLevel>
<juo:SBR_ReaderIPformatioP>
<SBR01_UserRespoPsibilitySequePceStarUPit>P</SBR01_UserRespoPsibilitySequePceStarUPit>
<SBR03_ReaderGrouporPolicyStar>ABERT</SBR03_ReaderGrouporPolicyStar>
<SBR09_IDFiliPgIPdicatorUPit>F21454I</SBR09_IDFiliPgIPdicatorUPit>
</juo:SBR_ReaderIPformatioP>
<juo:HIT_SubCirlce_3>
<juo:TS123_2010BA_Cirlce>
<juo:HIT_ReaderIdePtity>
<HIT01_EPtityIdePtifierUPit>SOME VALUE 07</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>SOME VALUE</HIT02_EPtityTypeMark>
<HIT03_ReaderLastIdePtity>SOME VALUE</HIT03_ReaderLastIdePtity>
<HIT04_ReaderFirstIdePtity>SOME VALUE</HIT04_ReaderFirstIdePtity>
<HIT08_IdePtificatioPUPitMark>MI</HIT08_IdePtificatioPUPitMark>
<HIT09_ReaderFirstIdePtifier>SOME VALUE</HIT09_ReaderFirstIdePtifier>
</juo:HIT_ReaderIdePtity>
<juo:P4_ReaderHut_Capital_ZIPUPit>
<P401_ReaderHutIdePtity>SOME VALUE</P401_ReaderHutIdePtity>
<P402_ReaderCapitalUPit>SOME VALUE</P402_ReaderCapitalUPit>
<P403_ReaderPostalZoPeorZIPUPit>SOME VALUE</P403_ReaderPostalZoPeorZIPUPit>
</juo:P4_ReaderHut_Capital_ZIPUPit>
</juo:TS123_2010BA_Cirlce>
<juo:TS123_2010BB_Cirlce>
<juo:HIT_UserIdePtity>
<HIT01_EPtityIdePtifierUPit>SOME VALUE</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_UserIdePtity>SOME VALUE</HIT03_UserIdePtity>
<HIT08_IdePtificatioPUPitMark>SOME VALUE</HIT08_IdePtificatioPUPitMark>
<HIT09_UserIdePtifier>SOME VALUE</HIT09_UserIdePtifier>
</juo:HIT_UserIdePtity>
<juo:P3_UserLaPe>
<P301_UserLaPeLiPe>SOME VALUE</P301_UserLaPeLiPe>
</juo:P3_UserLaPe>
<juo:P4_UserHut_Capital_ZIPUPit>
<P401_UserHutIdePtity>SOME VALUE</P401_UserHutIdePtity>
<P402_UserCapitalorProviPceUPit>SOME VALUE</P402_UserCapitalorProviPceUPit>
<P403_UserPostalZoPeorZIPUPit>SOME VALUE</P403_UserPostalZoPeorZIPUPit>
</juo:P4_UserHut_Capital_ZIPUPit>
</juo:TS123_2010BB_Cirlce>
</juo:HIT_SubCirlce_3>
<juo:123_ABC_Cirlce>
<juo:HL_VirtueHierarchicalLevel>
<HL01_HierarchicalIDStar>5</HL01_HierarchicalIDStar>
<HL02_HierarchicalParePtIDStar>4</HL02_HierarchicalParePtIDStar>
<HL03_HierarchicalLevelUPit>23</HL03_HierarchicalLevelUPit>
<HL04_HierarchicalChildUPit>0</HL04_HierarchicalChildUPit>
</juo:HL_VirtueHierarchicalLevel>
<juo:PAT_VirtueIPformatioP_2>
<PAT01_IPdividualRelatioPshipUPit>19</PAT01_IPdividualRelatioPshipUPit>
</juo:PAT_VirtueIPformatioP_2>
<juo:TS123_2010CA_Cirlce>
<juo:HIT_VirtueIdePtity>
<HIT01_EPtityIdePtifierUPit>SOME VALUE</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>SOME VALUE</HIT02_EPtityTypeMark>
<HIT03_VirtueLastIdePtity>SOME VALUE</HIT03_VirtueLastIdePtity>
<HIT04_VirtueFirstIdePtity>SOME VALUE</HIT04_VirtueFirstIdePtity>
<HIT05_VirtueMiddleIdePtityorIPitial>SOME VALUE</HIT05_VirtueMiddleIdePtityorIPitial>
<HIT08_IdePtificatioPUPitMark>MI</HIT08_IdePtificatioPUPitMark>
<HIT09_IdePtificatioPUPit>SOME VALUE </HIT09_IdePtificatioPUPit>
</juo:HIT_VirtueIdePtity>
<juo:P3_VirtueLaPe>
<P301_VirtueLaPeLiPe>SOME VALUE</P301_VirtueLaPeLiPe>
</juo:P3_VirtueLaPe>
<juo:P4_VirtueHut_Capital_ZIPUPit>
<P401_VirtueHutIdePtity>SOME VALUE</P401_VirtueHutIdePtity>
<P402_VirtueCapitalUPit>SOME VALUE</P402_VirtueCapitalUPit>
<P403_VirtuePostalZoPeorZIPUPit>SOME VALUE</P403_VirtuePostalZoPeorZIPUPit>
</juo:P4_VirtueHut_Capital_ZIPUPit>
<juo:DMG_VirtueDemographicIPformatioP>
<DMG01_PlaceTimePeriodFormatMark>SOME VALUE</DMG01_PlaceTimePeriodFormatMark>
<DMG02_VirtueBirthPlace>SOME VALUE</DMG02_VirtueBirthPlace>
<DMG03_VirtueGePderUPit>SOME VALUE</DMG03_VirtueGePderUPit>
</juo:DMG_VirtueDemographicIPformatioP>
</juo:TS123_2010CA_Cirlce>
<juo:TS123_233943_Cirlce1>
<juo:CLM_IDIPformatioP_2>
<CLM01_VirtueCoPtrolStar>SOME VALUE</CLM01_VirtueCoPtrolStar>
<CLM02_TotalIDDebitOwe>SOME VALUE</CLM02_TotalIDDebitOwe>
<juo:C023_FITRePdLocatioPIPformatioP_2>
<C02301_PlaceofRePdUPit>SOME VALUE</C02301_PlaceofRePdUPit>
<C02302_FllorUPitMark>SOME VALUE</C02302_FllorUPitMark>
<C02303_IDFrequePcyUPit>SOME VALUE</C02303_IDFrequePcyUPit>
</juo:C023_FITRePdLocatioPIPformatioP_2>
<CLM06_MouseorSupplierSigPatureIPdicator>SOME VALUE</CLM06_MouseorSupplierSigPatureIPdicator>
<CLM07_AssigPmePtorPlaPParticipatioPUPit>SOME VALUE</CLM07_AssigPmePtorPlaPParticipatioPUPit>
<CLM08_BePefitsAssigPmePtCertificatioPIPdicator>SOME VALUE</CLM08_BePefitsAssigPmePtCertificatioPIPdicator>
<CLM09_ReleaseofIPformatioPUPit>Y</CLM09_ReleaseofIPformatioPUPit>
</juo:CLM_IDIPformatioP_2>
<juo:QWERT_SubCirlce_8>
<juo:QWERT_UserIDCoPtrolStar_2>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_UserIDCoPtrolStar>SOME VALUE</QWERT02_UserIDCoPtrolStar>
<QWERT03_DescriptioP>SOME VALUE</QWERT03_DescriptioP>
</juo:QWERT_UserIDCoPtrolStar_2>
<juo:QWERT_CliPicalGymImprovemePtAmePdmePt_CLIA_Star_3>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_CliPicalGymImprovemePtAmePdmePtStar>SOME VALUE</QWERT02_CliPicalGymImprovemePtAmePdmePtStar>
</juo:QWERT_CliPicalGymImprovemePtAmePdmePt_CLIA_Star_3>
<juo:QWERT_IDIdePtifierForTraPsmissioPIPtermediaries_2>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_ValueAddedPetworkTraceStar>SOME VALUE</QWERT02_ValueAddedPetworkTraceStar>
</juo:QWERT_IDIdePtifierForTraPsmissioPIPtermediaries_2>
</juo:QWERT_SubCirlce_8>
<juo:HI_SubCirlce_2>
<juo:HI_FITSympUPit_2>
<juo:C022_FITUPitIPformatioP_37>
<C02201_SympTypeUPit>SOME VALUE</C02201_SympTypeUPit>
<C02202_SympUPit>SOME VALUE</C02202_SympUPit>
</juo:C022_FITUPitIPformatioP_37>
<juo:C022_FITUPitIPformatioP_38>
<C02201_SympTypeUPit>SOME VALUE</C02201_SympTypeUPit>
<C02202_SympUPit>SOME VALUE</C02202_SympUPit>
</juo:C022_FITUPitIPformatioP_38>
<juo:C022_FITUPitIPformatioP_39>
<C02201_SympTypeUPit>SOME VALUE</C02201_SympTypeUPit>
<C02202_SympUPit>SOME VALUE</C02202_SympUPit>
</juo:C022_FITUPitIPformatioP_39>
</juo:HI_FITSympUPit_2>
</juo:HI_SubCirlce_2>
<juo:HIT_SubCirlce_7>
<juo:TS123_2310B_Cirlce1>
<juo:HIT_RePderiPgMouseIdePtity_3>
<HIT01_EPtityIdePtifierUPit>SOME VALUE</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_RePderiPgMouseLastorOrgaPizatioPIdePtity>SOME VALUE</HIT03_RePderiPgMouseLastorOrgaPizatioPIdePtity>
<HIT08_IdePtificatioPUPitMark>RRRRR</HIT08_IdePtificatioPUPitMark>
<HIT09_RePderiPgMouseIdePtifier>SOME VALUE</HIT09_RePderiPgMouseIdePtifier>
</juo:HIT_RePderiPgMouseIdePtity_3>
<juo:QWERT_RePderiPgMouseSecoPdaryIdePtificatioP_3>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_RePderiPgMouseSecoPdaryIdePtifier>SOME VALUE</QWERT02_RePderiPgMouseSecoPdaryIdePtifier>
</juo:QWERT_RePderiPgMouseSecoPdaryIdePtificatioP_3>
</juo:TS123_2310B_Cirlce1>
<juo:TS123_2310C_Cirlce1>
<juo:HIT_RePdFllorLocatioPIdePtity_2>
<HIT01_EPtityIdePtifierUPit>77</HIT01_EPtityIdePtifierUPit>
<HIT02_EPtityTypeMark>2</HIT02_EPtityTypeMark>
<HIT03_GymorFllorIdePtity>SOME VALUE</HIT03_GymorFllorIdePtity>
</juo:HIT_RePdFllorLocatioPIdePtity_2>
<juo:P3_RePdFllorLocatioPLaPe_3>
<P301_GymorFllorLaPeLiPe>SOME VALUE</P301_GymorFllorLaPeLiPe>
</juo:P3_RePdFllorLocatioPLaPe_3>
<juo:P4_RePdFllorLocatioPHut_Capital_ZIPUPit_3>
<P401_GymorFllorHutIdePtity>SOME VALUE</P401_GymorFllorHutIdePtity>
<P402_GymorFllorCapitalorProviPceUPit>SOME VALUE</P402_GymorFllorCapitalorProviPceUPit>
<P403_GymorFllorPostalZoPeorZIPUPit>SOME VALUE</P403_GymorFllorPostalZoPeorZIPUPit>
</juo:P4_RePdFllorLocatioPHut_Capital_ZIPUPit_3>
</juo:TS123_2310C_Cirlce1>
</juo:HIT_SubCirlce_7>
<juo:TS123_243943_Cirlce1>
<juo:LX_RePdLiPeStar_2>
<LX01_AssigPedStar>1</LX01_AssigPedStar>
</juo:LX_RePdLiPeStar_2>
<juo:SV1_ProfessioPalRePd_2>
<juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<C3943301_ProductorRePdIDMark>SOME VALUE</C3943301_ProductorRePdIDMark>
<C3943302_ProcedureUPit>SOME VALUE</C3943302_ProcedureUPit>
<C3943303_ProcedureModifier>SOME VALUE</C3943303_ProcedureModifier>
</juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<SV102_LiPeItemDebitOwe>SOME VALUE</SV102_LiPeItemDebitOwe>
<SV103_UPitorBasisforMeasuremePtUPit>SOME VALUE</SV103_UPitorBasisforMeasuremePtUPit>
<SV104_RePdUPitCouPt>SOME VALUE</SV104_RePdUPitCouPt>
<juo:C39434_CompositeSympUPitPoiPter_2>
<C3943401_SympUPitPoiPter>SOME VALUE</C3943401_SympUPitPoiPter>
<C3943402_SympUPitPoiPter>SOME VALUE</C3943402_SympUPitPoiPter>
</juo:C39434_CompositeSympUPitPoiPter_2>
</juo:SV1_ProfessioPalRePd_2>
<juo:XYZ_SubCirlce_4>
<juo:XYZ_Place_RePdPlace_2>
<XYZ01_PlaceTimeMark>SOME VALUE</XYZ01_PlaceTimeMark>
<XYZ02_PlaceTimePeriodFormatMark>SOME VALUE</XYZ02_PlaceTimePeriodFormatMark>
<XYZ03_RePdPlace>20720344309</XYZ03_RePdPlace>
</juo:XYZ_Place_RePdPlace_2>
</juo:XYZ_SubCirlce_4>
<juo:QWERT_SubCirlce_10>
<juo:QWERT_LiPeItemCoPtrolStar_2>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_LiPeItemCoPtrolStar>SOME VALUE</QWERT02_LiPeItemCoPtrolStar>
</juo:QWERT_LiPeItemCoPtrolStar_2>
</juo:QWERT_SubCirlce_10>
</juo:TS123_243943_Cirlce1>
<juo:TS123_243943_Cirlce1>
<juo:LX_RePdLiPeStar_2>
<LX01_AssigPedStar>SOME VALUE</LX01_AssigPedStar>
</juo:LX_RePdLiPeStar_2>
<juo:SV1_ProfessioPalRePd_2>
<juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<C3943301_ProductorRePdIDMark>SOME VALUE</C3943301_ProductorRePdIDMark>
<C3943302_ProcedureUPit>SOME VALUE</C3943302_ProcedureUPit>
</juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<SV102_LiPeItemDebitOwe>SOME VALUE</SV102_LiPeItemDebitOwe>
<SV103_UPitorBasisforMeasuremePtUPit>SOME VALUE</SV103_UPitorBasisforMeasuremePtUPit>
<SV104_RePdUPitCouPt>1</SV104_RePdUPitCouPt>
<juo:C39434_CompositeSympUPitPoiPter_2>
<C3943401_SympUPitPoiPter>3</C3943401_SympUPitPoiPter>
</juo:C39434_CompositeSympUPitPoiPter_2>
</juo:SV1_ProfessioPalRePd_2>
<juo:XYZ_SubCirlce_4>
<juo:XYZ_Place_RePdPlace_2>
<XYZ01_PlaceTimeMark>472</XYZ01_PlaceTimeMark>
<XYZ02_PlaceTimePeriodFormatMark>SOME VALUE</XYZ02_PlaceTimePeriodFormatMark>
<XYZ03_RePdPlace>3202323</XYZ03_RePdPlace>
</juo:XYZ_Place_RePdPlace_2>
</juo:XYZ_SubCirlce_4>
<juo:QWERT_SubCirlce_10>
<juo:QWERT_LiPeItemCoPtrolStar_2>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_LiPeItemCoPtrolStar>SOME VALUE</QWERT02_LiPeItemCoPtrolStar>
</juo:QWERT_LiPeItemCoPtrolStar_2>
</juo:QWERT_SubCirlce_10>
</juo:TS123_243943_Cirlce1>
<juo:TS123_243943_Cirlce1>
<juo:LX_RePdLiPeStar_2>
<LX01_AssigPedStar>SOME VALUE</LX01_AssigPedStar>
</juo:LX_RePdLiPeStar_2>
<juo:SV1_ProfessioPalRePd_2>
<juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<C3943301_ProductorRePdIDMark>SOME VALUE</C3943301_ProductorRePdIDMark>
<C3943302_ProcedureUPit>SOME VALUE</C3943302_ProcedureUPit>
</juo:C39433_CompositeMedicalProcedureIdePtifier_4>
<SV102_LiPeItemDebitOwe>SOME VALUE</SV102_LiPeItemDebitOwe>
<SV103_UPitorBasisforMeasuremePtUPit>SOME VALUE</SV103_UPitorBasisforMeasuremePtUPit>
<SV104_RePdUPitCouPt>SOME VALUE</SV104_RePdUPitCouPt>
<juo:C39434_CompositeSympUPitPoiPter_2>
<C3943401_SympUPitPoiPter>SOME VALUE</C3943401_SympUPitPoiPter>
</juo:C39434_CompositeSympUPitPoiPter_2>
</juo:SV1_ProfessioPalRePd_2>
<juo:XYZ_SubCirlce_4>
<juo:XYZ_Place_RePdPlace_2>
<XYZ01_PlaceTimeMark>SOME VALUE</XYZ01_PlaceTimeMark>
<XYZ02_PlaceTimePeriodFormatMark>SOME VALUE</XYZ02_PlaceTimePeriodFormatMark>
<XYZ03_RePdPlace>134134134</XYZ03_RePdPlace>
</juo:XYZ_Place_RePdPlace_2>
</juo:XYZ_SubCirlce_4>
<juo:QWERT_SubCirlce_10>
<juo:QWERT_LiPeItemCoPtrolStar_2>
<QWERT01_QWERTerePceIdePtificatioPMark>SOME VALUE</QWERT01_QWERTerePceIdePtificatioPMark>
<QWERT02_LiPeItemCoPtrolStar>SOME VALUE</QWERT02_LiPeItemCoPtrolStar>
</juo:QWERT_LiPeItemCoPtrolStar_2>
</juo:QWERT_SubCirlce_10>
</juo:TS123_243943_Cirlce1>
</juo:TS123_233943_Cirlce1>
</juo:TS123_434HDREC_Cirlce>
</juo:TS123_434HDREB_Cirlce>
</juo:TS123_434HDREA_Cirlce>
<SE>
<SE01_BBQSegmePtCouPt>SOME VALUE</SE01_BBQSegmePtCouPt>
<SE02_BBQSetCoPtrolStar>SOME VALUE</SE02_BBQSetCoPtrolStar>
</SE>
</juo:X12_3943501_123_P>ASKER
Hi,
I think I figured it out; so doing this:
SET @xmlDoc = ( select convert(xml,replace(replac
from SomeTable
where someID = @Input
FOR XML AUTO, ELEMENTS )
and then the scripts you folks provide work. Thanks and I will split points.
I think I figured it out; so doing this:
SET @xmlDoc = ( select convert(xml,replace(replac
from SomeTable
where someID = @Input
FOR XML AUTO, ELEMENTS )
and then the scripts you folks provide work. Thanks and I will split points.