SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML VALUE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dyoung1006
Starting Member

USA
2 Posts

Posted - 11/06/2013 :  14:41:57  Show Profile  Reply with Quote
Im trying to do an exist or value not sure which on an xml field.

this is what i have

xml output:
<auditElement>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
</auditElement>

or
<propagationField>Family</propagationField>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
<field id="1037375" type="8" name="00_APC_Issues" formatstring="" />
<field id="1037379" type="8" name="00_BPC_Issues" formatstring="" />
<field id="1037380" type="8" name="00_CPC_Issues" formatstring="" />
<field id="1037385" type="8" name="00_DPC_Issues" formatstring="" />
<field id="1140067" type="5" name="10_SomeOTHERCODE" formatstring="" />
<field id="1627564" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627565" type="4" name="00_EFPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627566" type="4" name="00_LPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627567" type="4" name="00_PPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2155721" type="8" name="00_EPC_Issues" formatstring="" />
<field id="2155722" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190599" type="8" name="00_CPUBPC_Issues" formatstring="" />
<field id="2190600" type="8" name="00_LPUBPC_Issues" formatstring="" />
<field id="2190601" type="8" name="00_PPUBPC_Issues" formatstring="" />
<field id="2190603" type="4" name="00_CBUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190604" type="4" name="00_LPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2437178" type="4" name="00_PPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="3047764" type="8" name="00_FPUBPC_Issues" formatstring="" />
<field id="3051998" type="4" name="00_FPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
</auditElement>

what i would like to do is pull anything of the actual values of the <fieldID> and <setChoice> values to join them up and only pull back the records I really want.

how can I do this with the .value or.exists references? Any help is appreciated also any good reference material is appreciated.


this is what I have so far



DECLARE @test TABLE (RECID INT, DETAIL XML)
INSERT @test
SELECT ar.ID, ar.Details
FROM EDDSDBO.AuditRecord ar (NOLOCK)
JOIN @DocumentScope ds ON ar.ArtifactID = ds.DocumentArtifactID
JOIN @User u ON u.UserID = ar.UserID
WHERE ar.[Action] IN (3, 4, 5, 6)


SELECT * FROM @test t
--been playing with this
SELECT *, CAST(t.DETAIL AS XML).value('(./field@ID)','INT') FROM @test t

received this error
XQuery [@test.DETAIL.value()]: ")" was expected.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/07/2013 :  04:29:05  Show Profile  Reply with Quote

declare @x xml ='<auditElement><propagationField>Family</propagationField>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
<field id="1037375" type="8" name="00_APC_Issues" formatstring="" />
<field id="1037379" type="8" name="00_BPC_Issues" formatstring="" />
<field id="1037380" type="8" name="00_CPC_Issues" formatstring="" />
<field id="1037385" type="8" name="00_DPC_Issues" formatstring="" />
<field id="1140067" type="5" name="10_SomeOTHERCODE" formatstring="" />
<field id="1627564" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627565" type="4" name="00_EFPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627566" type="4" name="00_LPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627567" type="4" name="00_PPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2155721" type="8" name="00_EPC_Issues" formatstring="" />
<field id="2155722" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190599" type="8" name="00_CPUBPC_Issues" formatstring="" />
<field id="2190600" type="8" name="00_LPUBPC_Issues" formatstring="" />
<field id="2190601" type="8" name="00_PPUBPC_Issues" formatstring="" />
<field id="2190603" type="4" name="00_CBUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190604" type="4" name="00_LPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2437178" type="4" name="00_PPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="3047764" type="8" name="00_FPUBPC_Issues" formatstring="" />
<field id="3051998" type="4" name="00_FPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
</auditElement>'

SELECT m.n.value('(./@id)[1]','int') AS ID,
m.n.value('(./setChoice)[1]','varchar(100)') as SetChoice
FROM @X.nodes('/auditElement/field') m(n)

ID	SetChoice
-----------------------
1037373	1037392
1037375	NULL
1037379	NULL
1037380	NULL
1037385	NULL
1140067	NULL
1627564	NULL
1627565	NULL
1627566	NULL
1627567	NULL
2155721	NULL
2155722	NULL
2190599	NULL
2190600	NULL
2190601	NULL
2190603	NULL
2190604	NULL
2437178	NULL
3047764	NULL
3051998	NULL



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/07/2013 :  04:30:15  Show Profile  Reply with Quote
In your case since it comes from table column you need to use like


SELECT m.n.value('(./@id)[1]','int') AS ID,
m.n.value('(./setChoice)[1]','varchar(100)') as SetChoice
FROM @test t
CROSS APPLY DETAIL.nodes('/auditElement/field') m(n)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dyoung1006
Starting Member

USA
2 Posts

Posted - 11/07/2013 :  12:53:19  Show Profile  Reply with Quote
Thank you,visakh16!

I fixed this yesterday and didn't update my initial post.

I found some very helpful info from here:
http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

this was my final statement
--I ended up join on codes I was looking for to only pull back records I def wanted.
----Distinct list of Audit Record ID's
CREATE TABLE #AuditRecord
(RECID INT PRIMARY KEY)

INSERT INTO #AuditRecord (RECID)
SELECT DISTINCT RECID --DocumentArtifactID, UserName, [TimeStamp], ROW_NUMBER() OVER (PARTITION BY DocumentArtifactID ORDER BY [TIMESTAMP] ASC) AS 'RowNum'
FROM
(
SELECT
RECID,
c.value('(setChoice/text())[1]','int')'SetChoice',
c.value('(UnsetChoice/text())[1]','int') 'UnSetChoice'
FROM @DocumentScope ds CROSS APPLY DETAIL.nodes('/auditElement/field') AS t(c)
) su
JOIN #Codes
ON CodeId = su.SetChoice
OR CodeId = su.UnsetChoice
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000